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

    Unanswered: How to join tables in a way that make sense?

    I am joining multiple tables, below are just my join statements. I wonder if it makes senses the order I join them. Please let me know, thank you.


    FROM [Opt].[EDA].[I_DETAIL] as i_detail


    full join [Opt].[EDA].[CONTRACT] as contract

    on [contract].[CONTNUMBER]=[I_DETAIL].[CONTNUMBER]


    full join [Opt].[EDA].[REGION] as region

    on [REGION]. [SYSCODE]=[I_DETAIL].[REGIONSYSCODE]
    and [REGION].[RWITHINREGION]=[I_DETAIL].[RLUNITCODE]


    full join [Opt].[EDA].[R_TYPE] as r_type

    on [R_TYPE].[RTYPECODE] =[I_DETAIL] .[RTYPECODE]
    and [R_TYPE].[RCATEGORYCODE]=[I_DETAIL].[RCATEGORYCODE]



    full join [Opt].[EDA].[COMMODITY] as commodity

    on [COMMODITY].[COMCODE]= [I_DETAIL].[SECONDCOMCODE]



    full join [Opt].[EDA].[ADVERTISER]as advertiser

    on [advertiser].[ADVERTISERID]=[CONTRACT].[CUSTOMERNUMBER]


    where CASTMONTH=201106




    Go

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Not unless you specify
    Code:
    option (force order)
    In that case, SQL Server will join the tables in the order you specify them. If force order is not specified, it is up to the query optimizer to choose the order of the tables, and I'd say it normally does so in an efficient manner.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Thanks, does this work in 2008? I have syntax error for some reason, dunno why.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As far as I knwo, there is nothing wrong with that join. What (precise) error message do you get?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mangolili View Post
    I wonder if it makes senses the order I join them.
    i gotta ask...

    why are you using FULL joins? are you ~sure~ you don't want INNER joins? how about even LEFT OUTER?

    a bunch of FULL joins like that seems like such a shot in the dark...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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