January 5, 2006 at 5:56 am
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
January 5, 2006 at 6:52 am
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
January 6, 2006 at 9:31 am
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