CODEWITHSUNDEEP
mysql
KingOfKong
KingOfKong

Reputation: 279

SQL select rows where at least one value is something per id

I've looked at other answers to these questions and haven't seen one that fits exactly what I want.

So I have a table with ids and states and multiple records per id with different state values. How would I return a table only with the records where at least one record for an id is some value? so for example, if I have

 id|state
  1|CA
  1|ZZ
  1|ZZ
  2|NY
  2|CA
  3|NY
  4|IL
  4|ZZ

and I would like the ones where they had a record of being in NY

 id|state
  2|NY
  2|CA
  3|NY

Upvotes: 0

Views: 3536

Answers (1)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can try this:

SELECT *
FROM tablename
WHERE id IN (SELECT id FROM tablename WHERE state = 'NY');

Upvotes: 7

Related Questions