NoTheOtherFry NoTheOtherFry - 3 months ago 9
SQL Question

SQL Find all rows with foreign key 1 when any of foreign key 2 appear in second table

I'm up against the limit of my query writing expertise.
I have the following table in which the combination of extid + extdt us a sort of compound key:

ents
entid | extid | extdt | itemid |
=======================================
1000 | 100 | '2016-08-01' | 1 |
1001 | 100 | '2016-08-01' | 2 |
1002 | 200 | '2016-08-01' | 3 |
1003 | 100 | '2016-08-02' | 4 |
1004 | 200 | '2016-08-02' | 5 |
1005 | 100 | '2016-08-02' | 6 |


So if itemid (1 or 2)are in the items table, the query will return both row 1000 and 1001. If itemid 3 exists, row 1002 is returned and so on...

items
itemid | itemDesc |
===================
1 | 'fu' |
3 | 'bar' |
4 | 'blah' |


With the above items table, I would expect to get back :

entid | extid | extdt | itemid |
=======================================
1000 | 100 | '2016-08-01' | 1 |
1001 | 100 | '2016-08-01' | 2 |
1002 | 200 | '2016-08-01' | 3 |
1003 | 100 | '2016-08-02' | 4 |
1005 | 100 | '2016-08-02' | 6 |


I can't think of an aggregate function that would do what I'm looking for, nor does it seem like ANY/EXISTS would work. I'm getting hung up on the grouping the itemids... Could anyone please point me in the right direction?

Answer

First you need get the composite keys matching your items, but include DISTINCT to avoid duplicates

SELECT  DISTINCT extid,  extdt
FROM ents
JOIN items
  ON ents.itemid = items.itemid 

Now you retrive every row matching the selected composite key

SELECT *
FROM ents
JOIN ( SELECT  DISTINCT extid,  extdt
       FROM ents
       JOIN items
         ON ents.itemid = items.itemid 
     ) comp_key
 ON ents.extid = comp_key.extid
AND ents.extdt = comp_key.extdt