Hi Edward,
(see below)
Edward Corbett wrote:
Hi,
I am trying to join a bunch of tables together. I want a row for each
"learner", and on the row, I want some "user", "centre", "client" and
"centreManager" information if there is any. Thus, I am trying to outer join
from the "learner" table to 4 other tables. The query I have so far is below
but it is returning 0 rows where I know that learner 215 exists and it
should return a line with the "l1" information on it. Any help would be very
much appreciated.
select
`vPamUser`.`initials`,
l1.`learnerId`,
l1.`usedName`,
l1.`fullFirstNames`,
l1.`surname`,
`vPamCentre`.`centreName`,
`vPamCentreManager`.`managerName`,
`vPamCentreManager`.`managerUsedName`,
`vPamClient`.`clientShortName`
from
`vPamLearner` l1 left join vPamUser on l1.`assessorId` =
`vPamUser`.`userId`,
`vPamLearner` l2 left join vPamCentre on l2.`centreId` =
`vPamCentre`.`centreId`,
`vPamLearner` l3 left join vPamClient on l3.`clientId` =
`vPamClient`.`clientId`,
`vPamLearner` l4 left join vPamCentreManager on l4.`centreManagerId` =
`vPamCentreManager`.`centreManagerId`
where
l1.`learnerId` = 215
and l1.learnerId = l2.learnerId
and l1.learnerId = l3.learnerId
and l1.learnerId = l4.learnerId
;
You only needed to reference your `vPamLearner` table once and use no
commas like this:
SELECT
`vPamUser`.`initials`,
l1.`learnerId`,
l1.`usedName`,
l1.`fullFirstNames`,
l1.`surname`,
`vPamCentre`.`centreName`,
`vPamCentreManager`.`managerName`,
`vPamCentreManager`.`managerUsedName`,
`vPamClient`.`clientShortName`
FROM `vPamLearner` l1
LEFT JOIN vPamUser
ON l1.`assessorId` =`vPamUser`.`userId`
LEFT JOIN vPamCentre
ON l1.`centreId` = `vPamCentre`.`centreId`
LEFT JOIN vPamClient
ON l1.`clientId` = `vPamClient`.`clientId`
LEFT JOIN vPamCentreManager
ON l1.`centreManagerId` = `vPamCentreManager`.`centreManagerId`
WHERE l1.`learnerId` = 215
Each new JOIN looks at the tables that preceded it in the statement as
potential join targets. There is no need to create a self-join of the
parent table for each child table you want to join. This works for both
inner and outer joins.
In reference to your follow-up post, I do not understand how removing
the status reference from this query would have solved the problem
unless there were a few additional elements to this query you also left out.
Please let me know if this streamlined format improves your query speed
and result quality.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
