MySql Left Join Where Column Is Not Value

well, usually ppl do stuff like
SELECT A.* FROM A
LEFT JOIN B ON A.id = B.id_of_A
WHERE B.X IS NOT NULL

but i had a better situation
i had multiple left joins and one of the tables had all kind of values where i needed on of them so its like

SELECT A.*, C.c1, D.c1
IF (B.value = 'myVal', 1, 0) AS bval
 FROM A
LEFT JOIN B ON A.id = B.id_of_A
LEFT JOIN C ON A.id = C.id_of_A
LEFT JOIN D ON C.id = D.id_of_C

so if u out some extra left joins there OR A->B is 1->n our if is not so good like
Table B:
id_of_A | value
1  | 1
1  | 2
1  | 3
1  | 4
1  | 5
2  | 4
2  | 5

and what if i want to WHERE this IF (WHERE bval = 1)?
maybe some1 thinks right join but that will cut the rest of my info

so i need a left join that is a right join ha ha!!

well we can make it work like this:

SELECT A.*, C.c1, D.c1, B.value
FROM A
LEFT JOIN B ON A.id = B.id_of_A
                        AND B.value = 'myVal'
LEFT JOIN C ON A.id = C.id_of_A
LEFT JOIN D ON C.id = D.id_of_C

WHERE B.value IS (NOT) NULL

what's the difference u ask?
without the [AND B.value = 'myVal'] B.value could be [1-5] and i want where its not [2] so null would return even if is has [3], but now B.value is selected only if the value is [2] so if there is a row with value [3] it will come as a null

want more?
LEFT JOIN B ON A.id = B.id_of_A
     AND B.value = '1' AS BofOne
LEFT JOIN B ON A.id = B.id_of_A
     AND B.value = '2' BofTwo
 

Comments

Popular posts from this blog

OverTheWire[.com] Natas Walkthrough - JUST HINT, NO SPOILERS

SOLVED The item could not be indexed successfully because the item failed in the indexing subsystem

Asp.Net Ending Response options, Response.End() vs CompleteRequest()