Outer Joins

  • SQL2005 does not support old outer join syntax (=*).  So I'm rewriting queries to use the new standard.

    In my case, I've got an APARTMENT table that may or may not have an APARTMENT_TOUR or a PRESENTATION associated with it.  Using old outer join syntax, I could easily outer join apartment table to both child tables:

    select a.apartment_id,p.description, apt.tour_type

    from   apartment a, presentation p, apartment_tour apt

    where  a.apartment_id *= p.apartment_id and

              a.apartment_id *= apt.apartment_id

    However, new syntax doesn't seem to support a table being outer joined to two others.   How can I add APARTMENT_TOUR to be outer joined to APARTMENT in the query below?

    select a.apartment_id,p.description

    from   apartment a left outer join

     presentation p on a.apartment_id = p.apartment_id

  • select a.apartment_id, p.description

    from apartment a left outer join

     presentation p on a.apartment_id = p.apartment_id left outer join apartment_tour t on a.apartment_id = t.apartment_id

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • You can get SQL Server to do the translation for you.  Open the table as a query in Enterprise Manager and drop your old-style query into the SQL panel.  It will automatically be rewritten with LEFT OUTER JOIN.  (Assuming there are no CASE statements or other features not supported by this tool.)

    I created the apartment, presentation, and apartment_tour tables in a database, pasted the query from your post into an Enterprise Manager query, and it turned into this:

    SELECT     a.apartment_id, p.description, apt.tour_type

    FROM         apartment a LEFT OUTER JOIN

                          presentation p ON a.apartment_id = p.apartment_id LEFT OUTER JOIN

                          apartment_tour apt ON a.apartment_id = apt.apartment_id

    It's the same join structure that Phil posted, but it was done automatically.  I always have to edit these queries because I hate the spacing and line breaks, but you can be pretty confident that it is a correct translation of all your joins.  One other annoyance is it will convert "WHERE a AND (b OR c)" to "WHERE (a AND b) OR (a AND c)" because of the way the filter conditions are rearranged to fit the field grid.  While I don't like the way it looks, it is still a correct expression of the logic.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply