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!
SELECTcb.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
FROMSignList AS sl
LEFT JOINChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOINChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHEREsl.BatchID = '2333'
ORDER BYsl.BatchID
-- Not ok. ChurchList table is treated as INNER JOIN
SELECTcb.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
FROMSignList AS sl
LEFT JOINChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOINChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHEREcl.BatchID = '2333'
ORDER BYcl.BatchID
-- Not ok. ChurchBatch table is treated as INNER JOIN
SELECTcb.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
FROMSignList AS sl
LEFT JOINChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOINChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
WHEREcb.BatchID = '2333'
ORDER BYcb.BatchID
-- Ok. ChurchList table is now treated as LEFT JOIN
SELECTcb.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
FROMSignList AS sl
LEFT JOINChurchList AS cl ON cl.ModuleID = sl.ModuleID
AND cl.BatchID = '2333'
LEFT JOINChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
ORDER BYcl.BatchID
-- Ok. ChurchBatch table is now treated as LEFT JOIN
SELECTcb.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
FROMSignList AS sl
LEFT JOINChurchList AS cl ON cl.ModuleID = sl.ModuleID
LEFT JOINChurchBatch AS cb ON cb.ModuleID = sl.ModuleID
AND cb.BatchID = '2333'
ORDER BYcb.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