CODEWITHSUNDEEP
mysqlleft-join
theking963
theking963

Reputation: 2226

mySQL Left Join

I have two tables T1L and T2U. For records matching the left join, if Deleted!=0 I don't want those records from either tables. 

SELECT
    IF(U.Column1 IS NULL, L.id, -U.id) AS id,
    IF(U.Column1 IS NULL, L.Column1, U.Column1) AS Column1,
    IF(U.Column1 IS NULL, L.Column2, U.Column2) AS Column2,
    IF(U.Column1 IS NULL, L.Deleted, U.Deleted) AS Deleted
FROM
    T1 L
LEFT JOIN T2 U
    ON L.Column1 = U.Column1 AND L.Column2=U.Column2 AND U.Deleted=0

If Deleted!=0, currently it will give me the record from TI. I don't want that record at all from either TI or T2.

How can I modify the query to achieve that?

Upvotes: 1

Views: 200

Answers (2)

user610217
user610217

Reputation:

If I understand what you're trying to do properly, you should remove the "AND U.Deleted=0" in the join add a WHERE clause:

WHERE U.Deleted=0 OR U.Column1 IS NULL

Upvotes: 0

Derek
Derek

Reputation: 23318

Move it to the WHERE clause and add an additional check for NULL.

SELECT
    IF(U.Column1 IS NULL, L.id, -U.id) AS id,
    IF(U.Column1 IS NULL, L.Column1, U.Column1) AS Column1,
    IF(U.Column1 IS NULL, L.Column2, U.Column2) AS Column2,
    IF(U.Column1 IS NULL, L.Deleted, U.Deleted) AS Deleted
FROM
    T1 L
LEFT JOIN T2 U
    ON L.Column1 = U.Column1 AND L.Column2=U.Column2 
WHERE
    U.Deleted=0
    OR U.Deleted IS NULL

Upvotes: 2

Related Questions