Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    Unanswered: Joining more than 2 tables, e.g. 3?

    I tried to join three tables together. Anyone know the syntax for that. Please let me know, thanks.

    Below is an example of what I have, there's an error near "where", a blue line under the word.


    SELECT

    [Opt].[EDA4010].[CONTRACT].[COMPANYENTITYCODE]
    ,[Opt].[EDA4010].[INVOICE].[INVOICENUMBER]
    ,[Opt].[EDA4010].[INVOICE].[BROADCASTMONTH]
    ,[Opt].[EDA4010].[LOGS].[LOGKEY]
    ,[Opt].[EDA4010].[LOGS].[DATETIMEOFLOG]



    FROM [Opt].[EDA4010].[CONTRACT]
    join [Opt].[EDA4010].[INVOICE]
    Join [Opt].[EDA4010].[LOGS]

    where [Opt].[EDA4010].[CONTRACT].[CONTRACTNUMBER]=
    [Opt].[EDA4010].[INVOICE].[CONTRACTNUMBER]

    And [Opt].[EDA4010].[INVOICE].[CUSTOMERNUMBER]=
    [Opt].[EDA4010].[LOGS].[CUSTOMERNUMBER]

    Order by [Opt].[EDA4010].[CONTRACT].[COMPANYENTITYCODE]


    Go

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you use JOIN syntax, you should always use the ON clause
    Code:
    SELECT contract.companyentitycode
         , invoice.invoicenumber
         , invoice.broadcastmonth
         , logs.logkey
         , logs.datetimeoflog
      FROM opt.eda4010.contract AS contract
    INNER
      JOIN opt.eda4010.invoice AS invoice
        ON invoice.contractnumber = contract.contractnumber
    INNER
      JOIN opt.eda4010.logs AS logs
        ON logs.customernumber = invoice.customernumber
    ORDER 
        BY contract.companyentitycode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Thank you. I have a question on filtering the data by month. Where can I add the where clause or group by statement if I want to filter by month.

    For example, a column is call broadcast month: Nov-2009

    Please see highlighted area, thank you


    SELECT contract.companyentitycode
    , invoice.invoicenumber
    , invoice.broadcastmonth
    , logs.logkey
    , logs.datetimeoflog
    FROM opt.eda4010.contract AS contract
    INNER
    JOIN opt.eda4010.invoice AS invoice
    ON invoice.contractnumber = contract.contractnumber
    INNER
    JOIN opt.eda4010.logs AS logs
    ON logs.customernumber = invoice.customernumber


    where BROADCASTMONTH = NOV-2009


    ORDER
    BY contract.companyentitycode

    I got some syntax error when I do that. Please help thanks.

  4. #4
    Join Date
    Jul 2011
    Posts
    78
    Never mind, I got it. Just need to add a single colon to the Nov-2011.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •