December 18, 2007 at 1:16 pm
I have this query and was hoping someone could let me know if it's properly formed according to SQL syntax:
SELECT cb.batchid, sl.SName, sl.SType, sl.SDepartment, sl.OfficeHours, sl.SNotes,
sl.SObjectives, sl.SComments, cl.cGraduatingYear, cl.cOptionType, cl.cDepartment1, cl.cDepartment2,
cl.cStartDate, cl.cEndDate, cl.cObjectives, cl.cNotes, cl.cDiscipline1, cl.courseDiscipline2,
cl.cDiscipline3, cl.cDiscipline4, cl.cDiscipline5
FROM signlist sl
LEFT JOIN churchlist cl ON sl.ModuleID = cl.ModuleID
LEFT JOIN churchbatch cb ON sl.ModuleID = cb.ModuleID
WHERE batchid = '2333'
ORDER BY batchid
Thanks
December 18, 2007 at 1:26 pm
The only thing missing is the table alias before the two references to BATCH column.
N 56°04'39.16"
E 12°55'05.25"
December 19, 2007 at 7:38 am
Thanks...so other than that, it looks good?
Thank you!
December 19, 2007 at 1:02 pm
Yes.
It depends on the table alias for BATCH columns.
If the alias is one of the two LEFT JOINS, the LEFT JOIN is treated as an INNER JOIN instead.
N 56°04'39.16"
E 12°55'05.25"
December 19, 2007 at 1:06 pm
-- Ok!
SELECT cb.batchid,
sl.SName,
sl.SType,
sl.SDepartment,
sl.OfficeHours,
sl.SNotes,
sl.SObjectives,
sl.SComments,
cl.cGraduatingYear,
cl.cOptionType,
cl.cDepartment1,
cl.cDepartment2,
cl.cStartDate,
cl.cEndDate,
cl.cObjectives,
cl.cNotes,
cl.cDiscipline1,
cl.courseDiscipline2,
cl.cDiscipline3,
cl.cDiscipline4,
cl.cDiscipline5
FROM SignList AS sl
LEFT JOIN ChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOIN ChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHERE sl.BatchID = '2333'
ORDER BY sl.BatchID
-- Not ok. ChurchList table is treated as INNER JOIN
SELECT cb.batchid,
sl.SName,
sl.SType,
sl.SDepartment,
sl.OfficeHours,
sl.SNotes,
sl.SObjectives,
sl.SComments,
cl.cGraduatingYear,
cl.cOptionType,
cl.cDepartment1,
cl.cDepartment2,
cl.cStartDate,
cl.cEndDate,
cl.cObjectives,
cl.cNotes,
cl.cDiscipline1,
cl.courseDiscipline2,
cl.cDiscipline3,
cl.cDiscipline4,
cl.cDiscipline5
FROM SignList AS sl
LEFT JOIN ChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOIN ChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHERE cl.BatchID = '2333'
ORDER BY cl.BatchID
-- Not ok. ChurchBatch table is treated as INNER JOIN
SELECT cb.batchid,
sl.SName,
sl.SType,
sl.SDepartment,
sl.OfficeHours,
sl.SNotes,
sl.SObjectives,
sl.SComments,
cl.cGraduatingYear,
cl.cOptionType,
cl.cDepartment1,
cl.cDepartment2,
cl.cStartDate,
cl.cEndDate,
cl.cObjectives,
cl.cNotes,
cl.cDiscipline1,
cl.courseDiscipline2,
cl.cDiscipline3,
cl.cDiscipline4,
cl.cDiscipline5
FROM SignList AS sl
LEFT JOIN ChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOIN ChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHERE cb.BatchID = '2333'
ORDER BY cb.BatchID
-- Ok. ChurchList table is now treated as LEFT JOIN
SELECT cb.batchid,
sl.SName,
sl.SType,
sl.SDepartment,
sl.OfficeHours,
sl.SNotes,
sl.SObjectives,
sl.SComments,
cl.cGraduatingYear,
cl.cOptionType,
cl.cDepartment1,
cl.cDepartment2,
cl.cStartDate,
cl.cEndDate,
cl.cObjectives,
cl.cNotes,
cl.cDiscipline1,
cl.courseDiscipline2,
cl.cDiscipline3,
cl.cDiscipline4,
cl.cDiscipline5
FROM SignList AS sl
LEFT JOIN ChurchList AS cl ON cl.ModuleID = sl.ModuleID
AND cl.BatchID = '2333'
LEFT JOIN ChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
ORDER BY cl.BatchID
-- Ok. ChurchBatch table is now treated as LEFT JOIN
SELECT cb.batchid,
sl.SName,
sl.SType,
sl.SDepartment,
sl.OfficeHours,
sl.SNotes,
sl.SObjectives,
sl.SComments,
cl.cGraduatingYear,
cl.cOptionType,
cl.cDepartment1,
cl.cDepartment2,
cl.cStartDate,
cl.cEndDate,
cl.cObjectives,
cl.cNotes,
cl.cDiscipline1,
cl.courseDiscipline2,
cl.cDiscipline3,
cl.cDiscipline4,
cl.cDiscipline5
FROM SignList AS sl
LEFT JOIN ChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOIN ChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
AND cb.BatchID = '2333'
ORDER BY cb.BatchID
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply