mysql selection

Question

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 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!

Yahia · Accepted Answer · 2011-09-12 06:43:47Z

2

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

answered Sep 12, 2011 at 6:43

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

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

Comments

paulsm4 · Accepted Answer · 2011-09-12 06:44:15Z

1

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...

answered Sep 12, 2011 at 6:44

paulsm4

123k23 gold badges175 silver badges245 bronze badges

1 Comment

Majid Fouladpour Over a year ago

So you admit this is not an answer? Why don't you post it as a comment then?

Ed Heal · Accepted Answer · 2011-09-12 06:43:20Z

0

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

answered Sep 12, 2011 at 6:43

Ed Heal

60.3k18 gold badges91 silver badges137 bronze badges

Collectives™ on Stack Overflow

mysql selection

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related