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.

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

Answer 2

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