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!
Blank :
IF clause likes this;)\n\nSELECT table1.useriD, \n table1.name, \n table2.address, \n table2.employed, \n IF(table2.employed = 1, table3.jobTitle, null) AS additionalCol\nFROM table1 \nLEFT JOIN table2 ON table1.userID = table2.userID\nLEFT JOIN table3 ON table2.userID = table3.userID\n\n\nAlso you can use CASE WHEN:\n\nSELECT table1.useriD, \n table1.name, \n table2.address, \n table2.employed, \n CASE WHEN table2.employed = 1 THEN table3.jobTitle ELSE null END AS additionalCol\nFROM table1 \nLEFT JOIN table2 ON table1.userID = table2.userID\nLEFT JOIN table3 ON table2.userID = table3.userID\n",
2016-07-04T10:53:52
currarpickt :
SELECT table1.useriD, \n table1.name, \n table2.address, \n table2.employed, \n (IF table2.employed = 1, table3.jobTitle, null) as JobTitle\nFROM table1 \nLEFT JOIN table2 ON table1.userID = table2.userID\nLEFT JOIN table3 ON table2.userID = table3.userID\n\n\nReference: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html",
2016-07-04T10:54:07