CODEWITHSUNDEEP
mysql
soccer7
soccer7

Reputation: 4025

Whats wrong in this Query Mysql?

enter image description hereCan anybody tell me what is wrong in this mysql query? There is one record matching, so I guess that count should be 1 instead of 0.

SELECT c.bookmark_count
     , f.category_id cat_id
     , f.unfollow_at
     , CASE bookmark_count WHEN c.id = f.follower_category_id 
                           THEN 1 
                           ELSE 0 END count
     , c.id
     , f.follower_category_id follow_id
     , c.user_id 
  FROM categories c
  LEFT 
  JOIN following_follower_categories f 
    ON f.follower_category_id = c.id 
 WHERE c.user_id = 26;



bookmark_count  cat_id  unfollow_at          count    id    follow_id user_id
             4      72  0000-00-0000:00:00       0    172         l72   26
            10   NULL   NULL                     0    164        NULL   26
             1   NULL   NULL                     0    173        NULL   26
             9   NULL   NULL                     0    199        NULL   26
             3   NULL   NULL                     0    200        NULL   26
             3   NULL   NULL                     0    201        NULL   26
             1   NULL   NULL                     0    202        NULL   26
             0   NULL   NULL                     0    203        NULL   26
             3   NULL   NULL                     0    204        NULL   26
             0   NULL   NULL                     0    206        NULL   26
             0   NULL   NULL                     0    207        NULL   26
             0   NULL   NULL                     0    208        NULL   26
             0   NULL   NULL                     0    209        NULL   26
             1   NULL   NULL                     0    210        NULL   26

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The following expression is not syntactically correct in all databases but it does work in MySQL:

 (CASE bookmark_count WHEN c.id = f.follower_category_id 
                      THEN 1 
                      ELSE 0
  END) count

This is essentially equivalent to the following logic:

if c.id = f.follower_category_id and bookmark_count = 1 then 1
else if c.id <> f.follower_category_id and bookmark_count = 0 then 1
else 0

(This is ignoring NULL values for simplicity.)

That is, the expression c.id = f.follower_category_id is not treated as a condition, it is treated as an integer expression that is 1 for true and 0 for else.

I am not sure what you really want. Perhaps it is:

 (CASE WHEN c.id = f.follower_category_id 
       THEN bookmark_count
       ELSE 0
  END) count

Alternatively, if you just want a flag as to whether or not there is a match, then you can use:

(f.follower_category_id is not null) as count

Upvotes: 2

Related Questions