[jambrose00]

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!

[MrFujin]

Please post what you have tried.

[jambrose00]

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....

[MrFujin]

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.

[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 --

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

[jambrose00]

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

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:

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

[r937]

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)