mysql selection

I have one table - Users - and I have another table - Colors. Every user can create more records in Colors and each of these are "owned" by the user开发者_如何学Go in question (this means that Colors holds a field named "Userid", which is the same as the field named "Id" in the Users table).

Example: User Per has created these records in Colors: Red Blue Black

User Phil has created the record in Colors. Blue

Now I want to select the users that have created BOTH Blue and Red. Can I do this in one select statement?

Thanks!

try (example, modify according to your table structure!):

SELECT X.UserID FROM 
(SELECT UserID, COUNT(DISTINCT COLOR) C FROM COLORS WHERE COLOR IN ( 'Red', 'Blue' ) GROUP BY UserID) X 
WHERE X.C = 2

Sure you can :)

Hint: it'll probably involve a "join".

Q: What have you tried so far?

Q: Is this a homework assignment? We can help you find the answer - but we can't just GIVE you the answer...

Yes you can do this in one select statement. Perhaps offer the tables stucture and your attempt if you cannot get this to work.