php - join multiple table with condition according to column value in first table mysql
Hi I have eight tables and I have to get data from these all tables. Table structure is like as :
event_info id | event_id | event_types | animal_type human_victim_info id | event_id | victim_full_name | address livestock_destruction_info id | event_id | owner_name | owner_address crop_destruction_info id | event_id | crop_land_type proprty_destruction_info id | event_id | property_type | cost other_destruction_info id | event_id | description additional_info id | event_id | images | feedback wild_animal_info id | event_id | number | location_from | location_to
I have used join as :
SELECT e.* , wa.* , hv.* , lv.* , c.* , p.* , o.* , a.* , e.id as eId , e.event_id as eventId FROM event_info e LEFT JOIN wild_animal_info wa ON wa.event_id = e.event_id LEFT JOIN human_victim_info hv ON hv.event_id = e.event_id AND e.event_types LIKE '%1%' LEFT JOIN livestock_destruction_info lv ON lv.event_id = e.event_id AND e.event_types LIKE '%2%' LEFT JOIN crop_destruction_info c ON c.event_id = e.event_id AND e.event_types LIKE '%3%' LEFT JOIN proprty_destruction_info p ON p.event_id = e.event_id AND e.event_types LIKE '%4%' LEFT JOIN other_destruction_info o ON o.event_id = e.event_id AND e.event_types LIKE '%5%' LEFT JOIN additional_info a ON a.event_id = e.event_id WHERE e.event_id LIKE '%0026-%';
where I want to join human_victim_info table only if event_types in event_info table contains 1, similarly join livestock_destruction_info only if event_types in event_info table contains 2, similarly join crop_destruction_info only if event_types in event_info table contains 3 and so on upto other_destruction info. But I have to always join wild_animal_info and additional_info_table. I have event_id on each table. How could I do this?