Juned Ansari Juned Ansari - 1 year ago 62
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download