CODEWITHSUNDEEP
mysqlsql
revo
revo

Reputation: 48711

IF within WHERE

I wrote a query with a IF() statement within WHERE clause:

SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
    (
        SELECT  b.date_recorded
        FROM    wp_bp_activity as b
        WHERE   b.type IN ( 'activity_comment','activity_update' )
           AND IF(b.type = 'activity_comment', b.item_id, a.id) = a.id
        ORDER BY b.item_id desc
        limit 0,1
    ) as drecord
FROM wp_bp_activity as a
LEFT JOIN wp_users as u ON a.user_id = u.ID
WHERE
   a.type IN ( 'activity_update' )
order by cast(drecord as datetime) desc
limit 0,20

But it gives error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT b.date_recorded FROM wp_bp_activity as b WHERE b.' at line 3

What's the correct way of using IF like that?

Upvotes: 0

Views: 71

Answers (3)

exussum
exussum

Reputation: 18550

Do you even need the if ?

((b.type = 'activity_comment' AND b.item_id = a.id) OR (b.type <> 'activity_comment'))

seems to be what you are trying to do

The SQL error like others have said though is the missing comma

Upvotes: 1

Sumit Gupta
Sumit Gupta

Reputation: 2192

You miss a comma after u.display_name ... query will be 

SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name, 
(
    SELECT  b.date_recorded
    FROM    wp_bp_activity as b
    WHERE   b.type IN ( 'activity_comment','activity_update' )
       AND IF(b.type = 'activity_comment', b.item_id, a.id) = a.id
    ORDER BY b.item_id desc
    limit 0,1
) as drecord
FROM wp_bp_activity as a
LEFT JOIN wp_users as u ON a.user_id = u.ID
WHERE
  a.type IN ( 'activity_update' )
  order by cast(drecord as datetime) desc
 limit 0,20

hope this fix the issue... as I didn't check for other errors.

Upvotes: 1

Elon Than
Elon Than

Reputation: 9765

First thing to change is to add comma after u.display_name. It should help.

Upvotes: 1

Related Questions