user1575921 user1575921 - 7 months ago 12
SQL Question

how to do check if specific column null then ignore some part of query

how to do check if specific column null then ignore some part of query

I want to get result include these two row, but below query if

user_id
is null then not work

SELECT
mc.* FROM media_credit mc
LEFT JOIN credit c ON c.id = mc.credit_id
LEFT JOIN "user" u ON u.id = mc.user_id
LEFT JOIN "user" _u ON u.id = c.create_by_user_id
WHERE mc.media_id = $1
c.status = $2
AND u.status = $2 // how to make if mc.user_id is null then dont use this
AND _u.status = $2


table

media_credit
id | media_id | credit_id | username | user_id
1 | 1 | 1 | xx | null
2 | 1 | 1 | null | 1

user
id | status
1 | 0

credit
id | status | create_by_user_id
1 | 0 | 32

Answer

You should put the other conditions from your WHERE clause to the ON clause:

SELECT mc.* 
FROM media_credit mc
LEFT JOIN credit c 
    ON c.id = mc.credit_id
    AND c.status = $2
LEFT JOIN "user" u 
    ON u.id = mc.user_id
    AND u.status = $2
LEFT JOIN "user" _u 
    ON u.id = c.create_by_user_id
    AND _u.status = $2
WHERE 
    mc.media_id = $1

When you filter the right tables in the WHERE clause, your LEFT JOIN will be transformed into an INNER JOIN, thus dropping unmatched rows.