December 14th, 2013, 01:10 PM
-
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!
December 14th, 2013, 01:57 PM
-
Please post what you have tried.
December 14th, 2013, 02:00 PM
-
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....
December 14th, 2013, 02:21 PM
-
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.
December 14th, 2013, 02:31 PM
-
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
December 14th, 2013, 03:10 PM
-
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
December 14th, 2013, 03:29 PM
-
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)