Selecting multiple fields using an If condition, with a left join in mysql-throwing error
Any help is appreciated -
1064 - You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to 'as receiverimage, re.fName as receiver-name, re.lName as receiver-name, re.add' on line
SELECT co.*,
if(( co.senderid = 1) , ( re.image as receiverimage, re.fName as receiverfName , re.lName as receiverlName , re.address as receiverAddress , re.city as receivercity , re.state as receiverstate, re.country as receivercountry, re.zipCode as receiverzip),(se.address as senderAddress,se.city as sendercity , se.state as senderstate , se.country as sendercountry , se.zipCode as senderzip , se.image as senderimage ,se.fName as senderfName, se.lName as senderlName ))
FROM contacts as co
LEFT JOIN users as se ON( se.id = co.senderid )
LEFT JOIN users as re ON( re.id = co.receiverid )
where (senderid = 1 OR receiverid = 1)
- is the selection of multiple fields in the join table invalid or something else?
I cannot identify the exact cause, the underlying problem. here need to select multiple fields based on if condition from user table based on join.
source to share
You cannot use aliases in an if statement. I just tested your request and removing the aliases solves the problem.
if(( co.senderid = 1) , ( re.image, re.fName, re.lName, re.address, re.city, re.state, re.country, re.zipCode),(se.address,se.city, se.state, se.country, se.zipCode, se.image,se.fName, se.lName )) AS mydata
Edit: you must also add an alias for your entire IF so that you don't get the expression as a column name :)
Edit 2: Sorry, I wrote too early. You cannot select multiple rows like this in selection. You will need to write a subquery if you want this data or put an if for every column you need.
IF(co.senderid = 1, re.image, se.address,) as col1
IF(co.senderid = 1, re.fName, se.city) as col2
IF(co.senderid = 1, re.lName, se.state) as col3
IF(co.senderid = 1, re.address, se.country) as col4
And so on ... you understand :)
source to share