Juned Ansari Juned Ansari - 1 year ago 53
SQL Question

fetch other tables data based on if condition on particular field in one table in mysql

i have three tables

meeting (id,name,weekly_plan_id,is_agent)

weekly_plan (id,plan)

meeting_agent (meeting_id,agent_id) this is mapping table

meeting_contact (meeting_id,contact_id) this is mapping table

now what i want? to fetch table based on if condition in meeting tables is_agent field if it is 1 then i want to fetch data from
meeting_agent table else from meeting_contact

SELECT m.*,IF(m.is_agent='1',ma.agent_id, mc.contact_id) AS pid
meeting m
JOIN weekly_plan wp ON wp.id=m.weekly_plan_id
JOIN meeting_agent ma ON ma.meeting_id=m.id
JOIN meeting_contact mc ON mc.meeting_id=m.id
WHERE wp.id=9

Answer Source

Then you have to specify this logic over and over for each column :

       IF(m.is_agent='1',ma.agent_id, mc.contact_id) AS pid,
       IF(m.is_agent='1',ma.col1, mc.col1) ,
       IF(m.is_agent='1',ma.col2, mc.col2) ,

Or use LEFT JOIN with this condition inside the ON clause, and one of the tables columns will be null and the other will be filled with data.