SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
-
Jan 1, 2009, 22:28 #1
- Join Date
- Oct 2001
- Location
- TN
- Posts
- 79
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Help Selecting from 1 table only records that match multiple records in another
I am having trouble writing an SQL statement to select all the records in Table-A that have multiple corresponding records in Table-B that meet a requirement.
Example:
names
id | name
1 | bob
2 | chris
3 | sam
favoritecolors
id | nameid | color
1 | 1 | blue
2 | 1 | yellow
3 | 2 | blue
I would like to select all the name records of people that like both blue AND yellow, not just blue OR yellow. So the output of the select should be the record for bob and nobody else.
I figure a join of some kind would be used but I can't quite figure it out.
This works if i'm only looking for people that like one color:
Code:SELECT names.id, names.name FROM names JOIN favoritecolors ON names.id = favoritecolors.nameid WHERE favoritecolors.color = 'blue'
Code:SELECT names.id, names.name FROM names JOIN favoritecolors ON names.id = favoritecolors.nameid WHERE favoritecolors.color = 'blue' AND favoritecolors.color = 'yellow'
-
Jan 1, 2009, 22:44 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:SELECT n.name FROM `favorite-colors-table` AS f INNER JOIN `names-table` AS n ON n.id = f.nameid WHERE f.color IN ( 'blue','yellow' ) GROUP BY n.name HAVING COUNT(*) = 2
Bookmarks