#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0

    SQL Query regarding multiple joins


    Hi am struggling to create a query which will do the following:

    I have an `trainingevents` table with events information, which includes:
    TrainerID
    AssistantID
    The above are foreign keys to the `users` table, where the ID corresponds to the SID.

    Additionally there are also ID's to go to other tables.

    I need a query which when displayed on a PHP page, displays the Forname and Surname for both the trainer and assistant.

    I have tried multiple joins, and even tried FlySpeed SQL Query visual generator but to no avail.

    Any help would be appreciated!
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Please post what you have tried.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by MrFujin
    Please post what you have tried.
    Select
    trainingevents.*
    users.Forename,
    users.Surname,

    From
    trainingevents Inner Join
    users On trainingevents.TrainerID = users.SID And trainingevents.AssistantID =
    users.SID

    And other Inner Joins to join the tables together.
    I didn't get any results using one of them, and additionally I am not sure how I can abstract the Forename and Surname for both....
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    You try to match user Id to both the trainer and assistant at the same time. User Id is only one person. You will have two join the user table twice.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    in case you've never joined to the same table twice, you have to use tables aliases as well as column aliases, but it's pretty obvious once you've seen it --
    Code:
    SELECT trainingevents.*
         , CONCAT_WS(' ',trainers.Forename,trainers.Surname) AS trainer
         , CONCAT_WS(' ',assistants.Forename,assistants.Surname) AS assistant
      FROM trainingevents 
    INNER 
      JOIN users AS trainers
        ON trainers.SID = trainingevents.TrainerID
    INNER 
      JOIN users AS assistants
        ON assistants.SID = trainingevents.AssistantID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    in case you've never joined to the same table twice, you have to use tables aliases as well as column aliases, but it's pretty obvious once you've seen it --
    Code:
    SELECT trainingevents.*
         , CONCAT_WS(' ',trainers.Forename,trainers.Surname) AS trainer
         , CONCAT_WS(' ',assistants.Forename,assistants.Surname) AS assistant
      FROM trainingevents 
    INNER 
      JOIN users AS trainers
        ON trainers.SID = trainingevents.TrainerID
    INNER 
      JOIN users AS assistants
        ON assistants.SID = trainingevents.AssistantID
    Thank you very much for that!
    It looks easy now and this is what I have changed it to:

    Code:
    SELECT   locations.*, trainingtype.*,  
    	 
    trainingevents.*
         , CONCAT_WS(' ',trainers.Forename,trainers.Surname) AS trainer
         , CONCAT_WS(' ',assistants.Forename,assistants.Surname) AS assistant
     , trainingmodule1.TMDescription AS trainingmodule1
     , trainingmodule2.TMDescription AS trainingmodule2
      , trainingmodule3.TMDescription AS trainingmodule3
       , trainingmodule4.TMDescription AS trainingmodule4
      
      FROM trainingevents
     
    INNER 
      JOIN users AS trainers
        ON trainers.SID = trainingevents.TrainerID
    INNER 
      JOIN users AS assistants
        ON assistants.SID = trainingevents.AssistantID
    	
    INNER 
      JOIN trainingmodules AS trainingmodule1
        ON trainingmodule1.TMID = trainingevents.TrainingModule1
    INNER 
      JOIN trainingmodules AS trainingmodule2
        ON trainingmodule2.TMID = trainingevents.TrainingModule2
    INNER 
      JOIN trainingmodules AS trainingmodule3
        ON trainingmodule3.TMID = trainingevents.TrainingModule3
    INNER 
      JOIN trainingmodules AS trainingmodule4
        ON trainingmodule4.TMID = trainingevents.TrainingModule4
    
    INNER JOIN  `locations` ON trainingevents.LocID = locations.LocID
    INNER JOIN  `trainingtype` ON trainingevents.TrainingType = trainingtype.TTID
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    will there ever be a chance that not all of the 4 trainingmodule columns will have a value?

    if so, use LEFT OUTER JOINs

    better yet, redesigin the table so that it conforms to first normal form (there should be no repeating columns like those 4)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo