Juned Ansari Juned Ansari - 1 year ago 46
SQL Question

fetch data from table on the basis of condition in mysql

consider my scenario like i have 3 tables
table3 i want to fetch some colums from table2 or table3 on the basis of some condition


select tb1.*,
if(tb1.status='true' then tb2.name else tb3.name)
from table1 tb1, table2 tb2, table3 tb3
where tb1.aid=tb2.aid and tb1.aid=tb2.aid

in short i want to display some column from either table2 or table3 on the basis of condition

Answer Source


SELECT tb1.*,
       CASE WHEN tb1.status = 'true' THEN tb2.name ELSE tb3.name END as `name`
FROM table1 tb1
INNER JOIN table2 tb2 
 ON(t1.aid = tb2.aid)
INNER JOIN table3 tb3
 ON(tb1.aid = tb3.aid)

Or with IF() like you wanted :

SELECT tb1.*,
       IF(tb1.status='true' ,tb2.col1,tb3.col1) as col1,
       IF(tb1.status='true' ,tb2.col2,tb3.col2) as col2,
       IF(tb1.status='true' ,tb2.col3,tb3.col3) as col3

Also, try to avoid the use of implicit join syntax(comma separated) and use the proper syntax of a join, this will help you avoid mistakes like the one you did (compared both conditions to tb2 instead of one to tb2 and one to tb3