CODEWITHSUNDEEP
sqloracle-database
niceApp
niceApp

Reputation: 3143

Oracle get rows that exactly matches the list of values

I have a details table and I want to get the record that exactly matches the list of values in another table. Here is a scenario:

OrderDetailTable
OrderID ItemID
1        1
1        2
1        3
1        4
2        1
2        2
2        4
3        1
3        2
3        3
4        1
4        2


OrderedTable
 ItemID
    1
    2

Now I want to get the OrderID that has the exact ItemID matches with OrderedTable ItemID. In the above scenario OrderID 1 is valid since ItemID 1,2,3 is exactly matched with OrderedTable ItemID.

I used the join but it did not work. It gave me both OrderID 1,2 . How do I do it any ideas??

Upvotes: 0

Views: 3007

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Try this:

SELECT OrderID 
FROM OrderDetailTable JOIN OrderedTable USING (ItemID)
GROUP BY OrderID 
HAVING COUNT(DISTINCT ItemID) = (SELECT COUNT(DISTINCT ItemID) FROM OrderedTable)

The idea, in a nutshell, is as follows:

  • Count how many OrderDetailTable rows match OrderedTable by ItemID,
  • and then compare that to the total number of ItemIDs from OrderedTable.

If these two numbers are equal, the given OrderID "contains" all ItemIDs. If the one is smaller than the other, there is at least one ItemID not contained in the given OrderID.

Depending on your primary keys, the DISTINCT may not be necessary (though it doesn't hurt).

Upvotes: 1

Yahia
Yahia

Reputation: 70369

try

SELECT * FROM OrderDetailTable WHERE OrderID NOT IN
  (
  SELECT A.OrderID FROM
    (
    SELECT 
      Y.OrderID
      , OT.ItemID
      , (SELECT Z.ItemID 
         FROM OrderDetailTable Z 
         WHERE Z.ItemID = OT.ItemID AND Z.OrderID = Y.OrderID
        )  I 
    FROM OrderDetailTable Y, OrderedTable OT
  ) A 
WHERE A.I IS NULL);

EDIT - as per request the better syntax:

SELECT * FROM 
OrderDetailTable Z WHERE Z.ORDERID NOT IN 
(
SELECT O1 FROM 
(SELECT Y.ORDERID O1, YY.ORDERID O2 FROM 
 OrderDetailTable Y CROSS JOIN OrderedTable OT 
 LEFT OUTER JOIN OrderDetailTable YY ON 
 YY.ORDERID = Y.ORDERID AND YY.ITEMID = OT.ITEMID ) ZZ WHERE ZZ.O2 IS NULL);

Upvotes: 0

Related Questions