oracle sql compare result two subselects

Question

Let's say I have two Oracle SQL tables for my invoices. INV_HEAD for adress, date, ... Then I have INV_POS for every position of the invoice.

INV_HEAD
--------
id
date
adr_id
total

INV_POS
---------
id
he_id
pos
art_id
quantity
price

I can list all the invoices with

SELECT he.id, he.date, po.art_id, po.quantity, po.price 
FROM INV_HEAD he 
JOIN INV_POS po on po.he_id = he.id

Now I want to find invoices with the same positions, not necessarily in the same order. How can I do this?

As a result I only need the INV_HEAD.id of all invoices with the same positions.

Here is same sample data:

id | he_id | pos | art_id | quantity |   price
1  |     1 |   1 | 1000   |        5 |  100.00
2  |     1 |   2 | 2000   |       10 | 5000.00
3  |     2 |   1 | 2500   |        2 | 1250.00
4  |     3 |   1 | 2000   |       10 | 5000.00
5  |     3 |   2 | 1000   |        5 |  100.00

Invoice with he_id 1 and 3 have the same positions.

Answer 1

You want something like (note that the next query does not work, because we cannot compare sets using =):

SELECT SELECT DISTINCT H1.ID, H2.ID
  FROM INV_HEAD H1, INV_HEAD H2
  WHERE H1.ID <> H2.ID AND
    (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H1.ID) =
    (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H2.ID)

But we can rethink the problem: A = B also means that A-B UNION B-A is an empty set.

So instead of A = B you can use NOT EXISTS((A MINUS B) UNION (B MINUS A)) where A is (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H1.ID) and B is (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H2.ID)

So your query is:

SELECT DISTINCT H1.HE_ID, H2.HE_ID
  FROM INV_HEAD H1, INV_HEAD H2
  WHERE H1.ID <> H2.ID
  AND NOT EXISTS(
    ((SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H1.ID) 
      MINUS 
     (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H2.ID)) 
      UNION 
    ((SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H2.ID) 
     MINUS 
     (SELECT ART_ID, QUANTITY, PRICE FROM INV_POS WHERE HE_ID = H1.ID)));

This query creates pairs of invoices that have the same positions (note that if two invoices has no positions they are considered equals).

The condition H1.ID <> H2.ID avoids pairs like (1, 1) or (3, 3). But if you have the pair (1,3) you will also have the symmetric (3,1). If you want to avoid symmetry then use H1.ID < H2.ID or H1.ID > H2.ID instead.

If you want to know the invoices with the same position than a given invoice with ID = X then use WHERE H1.ID = X AND H1.ID <> H2.ID AND.. (use <>, never use < or > in this case).

Answer 2

You can use analytic function LISTAGG to concatenate id with same position

SELECT p.pos, LISTAGG(h.id, ', ') WITHIN GROUP (ORDER BY p.pos) "Id"
FROM inv_head h, inv_pos p
where h.id=p.he_id
group by p.pos;

You will get following results

POS Id

1 | 1, 2, 3

2 | 1, 3

I don't see the reason to join on inv_head, however I sticked to your original query (probably you are having some intention in this).