Outer joins
Posted: Thu Jan 30, 2003 11:04 pm
I am designing a report which has a series of tables that use a left outer join between two of them [TABLE1 and TABLE2]. TABLE2 then needs an inner join to a third table. When I run the report it gives an error:__"The table ^dbo.TABLE2^ is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause." ____If I make the second join an outer left join it gets a different error and if I try an outer right join it selects rows incorrectly. ____If I copy out the SQL select statement into Query Analyser I get the same error, but I can eliminate the problem by altering the syntax from Transact SQL to SQL-92 - eg from the T-SQL:__ ... WHERE TABLE1.REF *= TABLE2.REF ...____To the SQL-92:__ SELECT ... FROM TABLE1 LEFT JOIN TABLE2 ON (TABLE1.REF = TABLE2.REF) ...____My question is can I fix the problem using an Auto-SQL report format or is my only solution to create a User-SQL report? I have quickly tested this and it works fine, it^s just that I may need to change the layout in the future to add more tables/columns and I don^t want to start from scratch each time.____Thanks,____^pling