CODEWITHSUNDEEP
mysql
Turcia
Turcia

Reputation: 711

SQL query to set a value if data exist

Here's the fiddle; http://sqlfiddle.com/#!2/af8015/9

I've some data, and I want to set

I'm using following query;

select *,
    case cl.user_id
       when null then -1
       when im.user_id then 1
    end as result
from im
left join cl     on cl.user_id = im.user_id
                and cl.id_g = im.id_g
left join user   on user.user_id = im.user_id
left join g      on g.id_g = im.id_g

But, it returns null for -1, and I couldn't set 0 for the last case.

Expected result table is;

user id - g id - result
  1         1     1
  1         2     1
  1         3     0
  1         4     1
  2         1     1
  2         2     1
  2         3    -1
  2         4     0
...

Upvotes: 0

Views: 47

Answers (1)

O. Jones
O. Jones

Reputation: 108839

I can't see a way of generating the 0 result from your sample data.

I believe your result parameter should be computed like this;

IF (cl.user_id IS NOT NULL and cl.id_g IS NOT NULL, 1,
    IF(im.user_id IS NOT NULL and im.id_g IS NOT NULL, -1,0)
   ) result

And, I think your series of JOIN operations should go like this.

FROM user
LEFT JOIN im ON user.user_id = im.user_id
LEFT JOIN cl ON user.user_id = cl.user_id AND im.id_g = cl.id_g 
LEFT JOIN g  ON im.id_g = g.id_g

That is, you should lead with the user table. Here's an example: http://sqlfiddle.com/#!2/a5c6ef/1/0

Your result parameter is computed like this:

 case cl.user_id
    when null then -1
    when im.user_id then 1
 end as result

null values work strangely. A null value can never compare equal to anything, even another null value, so when null then -1 will never fire. This expression, on the other hand, should work.

 case when cl.user_id IS NULL then -1
      when im.user_id IS NULL then 1
      else 0 end as result

Upvotes: 1

Related Questions