CODEWITHSUNDEEP
sql
RegDHunter
RegDHunter

Reputation: 385

Finding rows with multiple properties

I have a table where the structure is:

+----+-------+----------+
| ID | Class | Names    |
+----+-------+----------+
| 1  | Paper |    Ed    |
+----+-------+----------+
| 2  | Glass |    Pete  |
+----+-------+----------+
| 3  | Glass |    Ed    |
+----+-------+----------+
| 4  | Rock  |    Fred  |
+----+-------+----------+

I want to find names who have a class of paper and glass. So in this little table it would return just:

+----+-------+----------+
| ID | Class | Names    |
+----+-------+----------+
| 1  | Paper |    Ed    |
+----+-------+----------+
| 3  | Glass |    Ed    |
+----+-------+----------+

However the name can't be part of the query. It has to be find names where class = paper and class = glass. So I only want names that have both paper and glass.

Simplified the SQL would be:

SELECT Names FROM table WHERE Class = Paper AND Class = Glass

Obviously this doesn't work thou as a row only has one class value.

TIA

Upvotes: 1

Views: 53

Answers (1)

John Woo
John Woo

Reputation: 263703

SELECT Names 
FROM table 
WHERE Class IN ('Paper','Glass') -- list of Class
GROUP BY Names
HAVING COUNT(*) = 2              -- number of class

in the case that Class is not unique in every names, a DISTINCT is require to count only unique value.

HAVING COUNT(DISTINCT Class) = 2

Upvotes: 1

Related Questions