CODEWITHSUNDEEP
mysqljoin
Palendrone
Palendrone

Reputation: 363

INNER JOIN ON IF Statement?

In Access I have a query that uses an Inner Join with and IF statement. Can I use this in MySQL or not as it doesn't seem to work as intended, not sure if there's something missing or if MySQL doesn't allow the method?

SELECT Matrix.Model, Payment.Cost 
FROM Payment
INNER JOIN Matrix ON if(Left(Matrix.Model,1)='S', Left(Matrix.Model,2), Left(Matrix.Model,1) = Matrix.PreFix)
GROUP BY Matrix.Model

I seem to get everything returned that doesn't start with an S as though the method isn't valid but not causing a major error.

Upvotes: 0

Views: 2172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

This is your query (using table aliases to make it more readable):

SELECT m.Model, p.Cost 
FROM Payment p INNER JOIN
     Matrix m
     ON if(Left(m.Model,1) = 'S', Left(m.Model, 2), Left(m.Model, 1) = m.PreFix)
GROUP BY m.Model;

Note that there are two possible returns from this statement: Left(m.Model, 2) and Left(m.Model, 1) = m.PreFix. This is a numeric context, so the then clause is interpreted as a number. Strings that start with non-numeric characters (such as S) are converted to 0, which is false.

I would write this without the if():

SELECT m.Model, p.Cost 
FROM Payment p INNER JOIN
     Matrix m
     ON (m.model like 'S%' and m.Prefix = Left(m.Model, 2)) or
        (m.model not like 'S%' and m.Prefix = Left(m.Model, 1))
GROUP BY m.Model;

Also, the column p.cost will be a value from an indeterminate row -- it is neither in the group by nor is it in an aggregation function. This construct would not work in any other database. It uses an extension to MySQL, whose use is generally discouraged unless you really understand what you are doing.

Upvotes: 2

Related Questions