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

Try this query :

select u_id, color, count(*) as total from table group by u_id, color having total = 1;
|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; sqlfiddle.com/#!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
|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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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