I am working on a photography project and I am facing a bit issue with joining tables and retrieving data from mysql database.
I have created two tables for this project. One table named cm_team is for team members and another table named cm_events for photography events..Assume to shoot a event, we require 6 persons and the id of the person is stored in cm_events table.
As you can see from the above images.. I am storing the id’s of members of cm_team in cm_events table.. I wish to obtain the name of the team member in the respective highlighted fields in the cm_events table..Any help is highly appreciated.
for example my desired output should be: instead of 5 under team_lead heading, I should get the name corresponding to 5 i.e Arjun
Advertisement
Answer
Something like this? (cleaner and faster than subqueries)
SELECT `event`.client_name, `event`.client_number, # some more event cols .. `team_lead`.`cm_name` AS `team_lead`, `candid_photo`.`cm_name` AS `candid_photo`, `candid_video`.`cm_name` AS `candid_video`, `traditional_photo`.`cm_name` AS `traditional_photo`, `traditional_video`.`cm_name` AS `traditional_video`, `helper`.`cm_name` AS `helper` FROM cm_events `event` JOIN cm_team `team_lead` ON `team_lead`.`cm_code` = `event`.`team_lead` JOIN cm_team `candid_photo` ON `candid_photo`.`cm_code` = `event`.`candid_photo` JOIN cm_team `candid_video` ON `candid_video`.`cm_code` = `event`.`candid_video` JOIN cm_team `traditional_photo` ON `traditional_photo`.`cm_code` = `event`.`traditional_photo` JOIN cm_team `traditional_video` ON `traditional_video`.`cm_code` = `event`.`traditional_video` JOIN cm_team `helper` ON `helper`.`cm_code` = `event`.`helper`