Tell me more ×
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. It's 100% free, no registration required.
up vote 0 down vote favorite

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 at 9:55
What do you mean with "only distinct colors for both users"? Why isn't 'Red' returned? – ypercube Feb 27 at 15:15
Hm... realy, why? May be because it is the same, not distinct? Means, that red is present in both users. – Ixtinkt Mar 5 at 14:59

2 Answers

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; sqlfiddle.com/#!2/b46c4/2/0 - live example – Ixtinkt Feb 27 at 9:39
@Ixtinkt If this answer is good, then up-vote it or mark it as the answer. – Marian Feb 27 at 9:53
up vote 0 down vote

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')? – Ixtinkt Feb 27 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 at 15:13

Your Answer

 
discard

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.