Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two instances user1 and user2. They have colors

u_id | color<br>
  1  | red<br>
  1  | green<br>
  2  | red<br>
  2  | blue<br>

I need a mysql request, that will returns only distinct colors for both users

Result example:

u_id | color<br>
 1   |  green<br>
 2   |  blue<br>
share|improve this question
I've changed the tags of the question, as it's related to a SELECT statement in MySQL. – Marian Feb 27 '13 at 9:55
What do you mean with "only distinct colors for both users"? Why isn't 'Red' returned? – ypercubeᵀᴹ Feb 27 '13 at 15:15
Hm... realy, why? May be because it is the same, not distinct? Means, that red is present in both users. – 1nstinct Mar 5 '13 at 14:59
up vote 0 down vote accepted

Try this query :

select u_id, color, count(*) as total from table group by u_id, color having total = 1;
share|improve this answer
Your example works correct, I made some modifications for fetching colors only for exact users. Thanks! SELECT u_id, color FROM colorinfo WHERE u_id in ('2', '3') GROUP BY color HAVING count(*) = 1;!2/b46c4/2/0 - live example – 1nstinct Feb 27 '13 at 9:39
@Ixtinkt If this answer is good, then up-vote it or mark it as the answer. – Marian Feb 27 '13 at 9:53

this is the example of SQL Server

        CREATE TABLE tempdb.dbo.##colorinfo (u_id int,color varchar(10))

        INSERT INTO  tempdb.dbo.##colorinfo VALUES(1,'red')
        INSERT INTO  tempdb.dbo.##colorinfo VALUES(1,'green')
        INSERT INTO  tempdb.dbo.##colorinfo VALUES(2,'red')
        INSERT INTO  tempdb.dbo.##colorinfo VALUES(2,'blue')

        SELECT  A.*
        FROM tempdb.dbo.##colorinfo A LEFT JOIN tempdb.dbo.##colorinfo B
        ON A.color=B.Color AND A.u_id<>b.U_ID
        WHERE B.u_id IS NULL
share|improve this answer
And if I whant to fetch data only for several users, where I have to inject WHERE u_id in ('1', '2')? – 1nstinct Feb 27 '13 at 9:31
@SandeepKumar, you can "fiddle" with MySQL at SQL-Fiddle if you don't have it installed. No need to post a SQL-Server answer for a MySQL question. The syntax is not much different (if at all) for simple queries. – ypercubeᵀᴹ Feb 27 '13 at 15:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.