CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
pillarOfLight
pillarOfLight

Reputation: 8982

using IF statement to get all fields depending on the LEFT JOIN

so I have this query

SELECT IF(c2.nid IS NULL, c.*, c2.*) FROM table1 c LEFT JOIN table1 c2 ON c.cid = c2.pid WHERE c.pid = 0 AND c.nid = 674662;

Notice that both c and c2 are referring to table1....basically, I want it so that if a left join entry exists, it will return the row in the left joined c2 whereas if it doesn't exists, it'll return the entries in c1...

However, executing this query will yield mysql syntax error..how do I go about accomplishing what im trying to accomplish (preferably in a single IF statement)?

Upvotes: 1

Views: 58

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

As @luksch answer, you can do this by using IF() or COALESCE() function on every column you need.

The only way that I can think of is with a UNION and splitting the two cases:

SELECT c2.*
FROM table1 c 
  INNER JOIN table1 c2 ON c.cid = c2.pid 
WHERE c.pid = 0 AND c.nid = 674662

UNION ALL

SELECT c.*
FROM table1 c 
  LEFT JOIN table1 c2 ON c.cid = c2.pid 
WHERE c.pid = 0 AND c.nid = 674662
  AND c2.pid IS NULL ;

Upvotes: 0

luksch
luksch

Reputation: 11712

I fear you can't use the star pattern within IF statement. So you need to explicitly type it out like

SELECT IF(c2.nid IS NULL, c.cid, c2.cid), 
       IF(c2.nid IS NULL, c.pid, c2.pid), ... 
FROM table1 c LEFT JOIN table1 c2 ON c.cid = c2.pid 
WHERE c.pid = 0 AND c.nid = 674662;

Upvotes: 2

Related Questions