CODEWITHSUNDEEP
mysqlif-statementjoinwhere-clause
Guppy
Guppy

Reputation: 426

MySQL with IF or EXISTS in WHERE

I have a problem with the following query for id’s not present in tableC. Despite the LEFT JOIN, an id in tableA and tableB is not in the results if the id is absent from tableC. And that is because the tableC.name doesn’t exist for these id’s. I imagined MySQL would then ignore the last part considering the clause to be true….but no.

SELECT
    tableA.id, tableA.name
FROM
    tableA
    LEFT JOIN tableB ON tableA.id = tableB.id
    LEFT JOIN tableC ON tableA.id = tableC.id
WHERE
    tableA.latin = 'whatever'
    AND RIGHT(tableC.name,2) != 'y'

I imagine there is a solution using either IF, CASE or EXISTS on the second part of the WHERE clause, but I don’t get the result I want with the following three attempts (I only show the last line):

WHERE
    tableA.latin = 'whatever'
    AND IF(tableC.name <> 0, RIGHT(tableC.name,2) != 'y', ' ');

This doesn’t give error either, but not the expected result:

WHERE
    tableA.latin = 'whatever'
    AND IF(tableC.name = true, RIGHT(tableC.name,2)!= 'y', ' ');

Trying the following with EXISTS gives me error in the MySQL Wordbench editor:

WHERE
    tableA.latin = 'whatever'
    AND if(EXISTS tableC.name, RIGHT(tableC.name,2) != 'y', ' ');

Upvotes: 1

Views: 73

Answers (3)

Flash_Dalton
Flash_Dalton

Reputation: 38

How about this (untested but I think it should work):

SELECT tableA.id, tableA.name FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON (tableA.id = tableC.id AND RIGHT(tableC.name,2) != 'y') 
WHERE tableA.latin =  'whatever'

It makes table c only include the ones you want but doesn't interfere with the left outer join.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Any time you reference a column (tableC.name in your case) from a left-joined table in the where clause, you force the join to behave as if it were an inner join. Instead, move the test into the join condition.

SELECT tableA.id, tableA.name 
    FROM tableA
        LEFT JOIN tableB 
            ON tableA.id = tableB.id
        LEFT JOIN tableC 
            ON tableA.id = tableC.id
                AND RIGHT(tableC.name,2) != 'y'
    WHERE tableA.latin =  'whatever'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

There is a much easier solution. Your where condition is turning the outer joins into inner joins. Just move the condition into the on clause:

SELECT tableA.id, tableA.name FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON tableA.id = tableC.id AND RIGHT(tableC.name, 2) <> 'y'
WHERE tableA.latin =  'whatever';

Of course, the expression RIGHT(tableC.name,2) != 'y' should always be true almost always, because you are looking for two characters and comparing to one. Perhaps you mean:

SELECT tableA.id, tableA.name FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON tableA.id = tableC.id AND tableC.name not like '%y'
WHERE tableA.latin =  'whatever';

Upvotes: 0

Related Questions