Chris Chris - 5 months ago 6
SQL Question

How to conditionally select certain fields in MySQL statement

I have a SELECT statement which takes various fields from different tables in my database using LEFT JOIN. I'd like to select additional fields only when a certain condition is met. Here's an example of what I'm trying to achieve.

SELECT table1.useriD,
table1.name,
table2.address,
table2.employed
(IF table2.employed = 1,
SELECT table3.jobTitle)
FROM table1
LEFT JOIN table2 ON table1.userID = table2.userID
LEFT JOIN table3 ON table2.userID = table3.userID


Just to clarify, the above example is completely made up, but it demonstrates what I'm trying to do. I know that the 'IF' statement in the middle is completely wrong - this is the bit I need help with. I've looked at MySQL IF statements and the MySQL IF function but can't figure out how it should work.

Any help much appreciated.

Thanks!

Answer

IF clause likes this;)

SELECT table1.useriD, 
       table1.name, 
       table2.address, 
       table2.employed, 
       IF(table2.employed = 1, table3.jobTitle, null) AS additionalCol
FROM table1 
LEFT JOIN table2 ON table1.userID = table2.userID
LEFT JOIN table3 ON table2.userID = table3.userID

Also you can use CASE WHEN:

SELECT table1.useriD, 
       table1.name, 
       table2.address, 
       table2.employed, 
       CASE WHEN table2.employed = 1 THEN table3.jobTitle ELSE null END AS additionalCol
FROM table1 
LEFT JOIN table2 ON table1.userID = table2.userID
LEFT JOIN table3 ON table2.userID = table3.userID