SQL Selecting rows with multiple values

Question

I have these 2 tables:

Table SW_ITEM:

ID  SWID  ITEM_ID
1    1      99
2    2      99
3    5      99
4    2      100
5    1      100
6    1      101
7    2      102

Table ITEM:

  ID   FILENAME
  99      abc
  100     def
  101     geh
  102     ijk

column ITEM_ID is a foreign key to the column ID of table ITEM.

So I want all filenames which have the SWID "1" AND "2" (that would be ITEMID 99 and 100, so their filenames are "abc" and "def")

Here I have to say that it is possible that ITEM_ID has more than one entry with the same SWID, so I cannot use this SQL:

SELECT ITEM_ID FROM SW_ITEM
WHERE SWID  IN (1,2) 
GROUP BY ITEM_ID
HAVING COUNT(ITEM_ID) = 2

So is there any other possibility to get all entries which have the SWID 1 and 2 (creating a join for every SWID is also not an option - because with many entries it would be really slow)

Kind regards

Answer 1

You need to use DISTINCT in COUNT and count SWID instead of ITEM_ID:

SELECT ITEM_ID FROM SW_ITEM
WHERE  SWID IN (1,2) 
GROUP  BY ITEM_ID
HAVING COUNT(DISTINCT SWID) = 2;

Please checkout this demo.

To retrieve all filenames, try:

SELECT ITEM_ID, FILENAME
FROM   ITEM JOIN SW_ITEM ON ITEM.ID = SW_ITEM.ITEM_ID
WHERE  SWID IN (1,2) 
GROUP  BY ITEM_ID
HAVING COUNT(DISTINCT SWID) = 2;

Demo