MySQL select "distinct/unique" records for two columns

Question

I have this table:

id  user    value
1   A       Cool
2   A       Cool
3   A       Cool
2   A       Warm
3   A       Warm
4   B       Cool
5   C       Cool
5   C       Warm

What I want to get here is the record that has the value of "Cool" and that it doesn't have "Warm". This can be identified by the ID and the User column.

I tried doing something like this:

SELECT DISTINCT id, user FROM log_table where value= 'Cool'

But this will still return the record that also has "Warm"

Expected output is:

id  user    value
1   A       Cool
4   B       Cool

Another problem I found with Distinct is that it doesn't let me add * or I don't how to do it since when I tried it was an error. Can I add concat after distinct as well? Without it being treated in the distinct function?

I may be using Distinct wrongly here.

why to select id ? adding id in expected output is ambiguous. As how will mysql know that you want id 1 not 2 for user A? — Omkar Mozar
– Omkar Mozar, Commented Jul 11, 2018 at 11:35
You are using distinct wrongly. The distinct applies to id AND user. — P.Salmon
– P.Salmon, Commented Jul 11, 2018 at 11:46
@P.Salmon I figure I did. I'm not that familiar with Distinct. sorry. — magicianiam
– magicianiam, Commented Jul 11, 2018 at 11:49

M Khalid Junaid · Accepted Answer · 2018-07-11 11:40:32Z

1

You could use conditional aggregation for your expected results

select id, user 
from log_table 
group by id, user 
having count(case when value= 'Cool' then 1 else null end) > 0
and  count(case when value= 'Warm' then 1 else null end) = 0

Demo

Or you could use exists

select id, user 
from log_table a
where a.value = 'Cool'
and not exists (
  select 1
  from log_table
  where a.id = id and a.user = user
  and value= 'Warm'
)

Demo

answered Jul 11, 2018 at 11:40

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

2 Comments

magicianiam Over a year ago

I did not know you can do that. I'll give it a try.

magicianiam Over a year ago

I think I would prefer the second one as there are multiple values I need to check to.

Strawberry · Accepted Answer · 2018-07-11 11:44:46Z

1

Consider the following:

SELECT * 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.user = x.user 
   AND y.value = 'warm' 
 WHERE x.value = 'cool';
+----+------+-------+------+------+-------+
| id | user | value | id   | user | value |
+----+------+-------+------+------+-------+
|  1 | A    | Cool  | NULL | NULL | NULL  |
|  2 | A    | Cool  |    2 | A    | Warm  |
|  3 | A    | Cool  |    3 | A    | Warm  |
|  4 | B    | Cool  | NULL | NULL | NULL  |
|  5 | C    | Cool  |    5 | C    | Warm  |
+----+------+-------+------+------+-------+

I'll leave the rest of the problem as an exercise for the reader.

answered Jul 11, 2018 at 11:44

Strawberry

34k14 gold badges43 silver badges58 bronze badges

3 Comments

magicianiam Over a year ago

Thank you. I did not know you can join on the same table. :)

Strawberry Over a year ago

@magicianiam If you couldn't, SQL would be an almost entirely useless construction!

magicianiam Over a year ago

I only know that you can join other tables, not the same one. :D thank you for this information.

Collectives™ on Stack Overflow

MySQL select "distinct/unique" records for two columns

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related