CODEWITHSUNDEEP
mysqldatabaseif-statementwhere-clause
Query Master
Query Master

Reputation: 7097

How to apply IF condition with WHERE CLAUSE in this Query Using MySql

This is my MySQL query and I have a week problem in this query. I don't know how to apply IF condition with WHERE clause.

Query:

SELECT
*,
IFNULL((SELECT ur.user_rating FROM user_rating ur 
WHERE ur.vid_id = v.id AND ur.user_id = '1000'),'NULL') AS user_rating
FROM videos v
WHERE WEEK(v.video_released_date) = WEEK(NOW())
AND  
v.`is_done` = 1
ORDER BY v.admin_listing ASC;

I want OR (how do I apply this condition with where clause?)

IF( WEEK(v.video_released_date) = WEEK(NOW()) , WEEK(NOW()) , WEEK(NOW())-1)
=
IF( WEEK(v.video_released_date) = WEEK(NOW()) , WEEK(NOW()) , WEEK(NOW())-1)

Briefing

If video released date has passed and not match with current week then previous week apply

Myself

When I was tried myself like this they return me whole data 

SELECT
*,
IFNULL((SELECT ur.user_rating FROM user_rating ur 
WHERE ur.vid_id = v.id AND ur.user_id = '1000'),'NULL') AS user_rating
FROM videos v
WHERE IF(WEEK(v.video_released_date) = WEEK(NOW()),WEEK(NOW()),WEEK(NOW())-1)
= IF(WEEK(v.video_released_date) = WEEK(NOW()),WEEK(NOW()),WEEK(NOW())-1) 
AND  
v.`is_done` = 1
ORDER BY v.admin_listing ASC;

What am I doing wrong in this query?

Upvotes: 3

Views: 7187

Answers (3)

Jeshurun
Jeshurun

Reputation: 23186

Use the CASE WHEN THEN ELSE END construct. You can read more about it here in the documentation.

SELECT *,
    IFNULL((SELECT ur.user_rating FROM user_rating ur 
        WHERE ur.vid_id = v.id AND ur.user_id = '1000'),'NULL') AS user_rating
    FROM videos v
    WHERE 
        WEEK(v.video_released_date) = 
            CASE WHEN WEEK(v.video_released_date) = WEEK(NOW()) 
            THEN WEEK(NOW()) ELSE WEEK(NOW())-1 END
    AND  
        v.`is_done` = 1
ORDER BY v.admin_listing ASC;

Upvotes: 1

JHS
JHS

Reputation: 7871

Try this - 

    SELECT *,
           IFNULL((SELECT ur.user_rating FROM user_rating ur 
                   WHERE ur.vid_id = v.id AND ur.user_id = '1000'),'NULL') AS user_rating
    FROM videos v
    WHERE WEEK(v.video_released_date) = IF(WEEK(v.video_released_date) = WEEK(NOW()),WEEK(NOW()),WEEK(NOW())-1) 
    AND  v.is_done = 1
    ORDER BY v.admin_listing ASC;

Upvotes: 5

Robin Castlin
Robin Castlin

Reputation: 10996

Well it looks like you compare x with x through these IF.

x = x is "always" true.

Upvotes: 1

Related Questions