phpmysqljoin

Need help in joining two tables from mysql database for a photography project

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.
enter image description here enter image description here

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

Solution

  • 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`