query formation

  • 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

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

  • Thanks...so other than that, it looks good?

    Thank you!

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

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