MYSQL query with if-else statement

Question

Hi I have below table structure shown in image

I am trying to write query in mysql to fetch

name,address,mono from customer table or vendortable depend on whoseid value from transportsticker table

I tried as

 SELECT transportsticker.* ,AA.name,AA.address,AA.mono FROM transportsticker INNER JOIN (case when (transportsticker.whoseid='vendor') then (vendortable) else (customertable) end) AA   ON AA.id=transportsticker.vorcid AND transportsticker.id=1

But it is giving syntax error. can anybody help me...?

Answer 1

CASE in SQL is an expression and cannot be used to control flow of execution like in procedural languages.

You can use LEFT JOIN with COALESCE instead:

SELECT t.*, 
       COALESCE(c.name, v.name), 
       COALESCE(c.address, v.address),
       COALESCE(c.mono, v.mono) 
FROM transportsticker AS t
LEFT JOIN customertable AS c 
   ON t.whoseid='customer' AND c.id=t.vorcid
LEFT JOIN vendortable AS v
   ON t.whoseid='vendor' AND v.id=t.vorcid
WHERE t.id=1