Jayshree Kharate Jayshree Kharate - 5 months ago 22
MySQL Question

MYSQL query with if-else statement

Hi I have below table structure shown in image

enter image description here

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

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