Finding rows with multiple properties

Question

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

John Woo · Accepted Answer · 2013-10-15 09:13:21Z

1

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

answered Oct 15, 2013 at 9:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding rows with multiple properties

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related