Odtnhj

I'm trying to work out a query that will find me all of the distinct Names whose LocationIDs are in a given set of ids. The catch is if any of the LocationIDs associated with a distinct Name are not in the set, then the Name should not be in the results.

Say I have the following table:

ID | LocationID | ... | Name
-----------------------------
1 | 1 | ... | A
2 | 1 | ... | B
3 | 2 | ... | B

I'm needing a query similar to

SELECT DISTINCT Name FROM table WHERE LocationID IN (1, 2);

The problem with the above is it's just checking if the LocationID is 1 OR 2, this would return the following:

A
B

But what I need it to return is

B

Since B is the only Name where both of its LocationIDs are in the set (1, 2)

You can try to write two subquery.

1. get count by each Name
   


2. get count by your condition.

then join them by count amount, which means your need to all match your condition count number.

Schema (SQLite v3.17)

CREATE TABLE T(
 ID int,
 LocationID int,
 Name varchar(5)

);

INSERT INTO T VALUES (1, 1,'A');
INSERT INTO T VALUES (2, 1,'B');
INSERT INTO T VALUES (3, 2,'B');

Query #1

SELECT t2.Name
FROM 
(
 SELECT COUNT(DISTINCT LocationID) cnt
 FROM T
 WHERE LocationID IN (1, 2)
) t1
JOIN 
(
 SELECT COUNT(DISTINCT LocationID) cnt,Name
 FROM T
 WHERE LocationID IN (1, 2)
 GROUP BY Name
) t2 on t1.cnt = t2.cnt;

| Name |
| ---- |
| B |

View on DB Fiddle

You can just use aggregation. Assuming no duplicates in your table:

SELECT Name
FROM table
WHERE LocationID IN (1, 2)
GROUP BY Name
HAVING COUNT(*) = 2;

If Name/LocationID pairs can be duplicated, use HAVING COUNT(DISTINCT LocationID) = 2.