Geek asks and answers

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 do not know how to apply the IF condition with a sentence WHERE.

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 a 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 the release date of the video has passed and does not coincide with the current week, then the previous week applies

Himself

When I was trying myself this way, 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?

+3
source share
3 answers

Try it -

    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;
+5
source

Well, it looks like you are comparing x with x through these IF.

x = x is always true.

+1

CASE WHEN THEN ELSE END. .

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;
+1

More articles:


All Articles