Restricted outer join on table also member of an inner join

[pondo]

I have a MS SQL Server 7 Table that has several inner joins (not nullable equi-joins).&nbsp;&nbsp;On the same table, I need to perform another join with yet another table, but this time with an outer join that would allow null records. <br><br>Right now I am performing these inner and outer joins using one view to consolidate both kinds.&nbsp;&nbsp;I get this &quot;Msg 300&quot; error not at the time of view creation, but when I run an actual query against the view.<br><br>Is there some kind of work-around for this? The same error happens even if I don't use a view for the join, but just a standard sql select statement at the prompt.&nbsp;&nbsp;&nbsp;<br><br>I can't find the SQL Server &quot;Msg 300&quot; error/solution description anywhere.&nbsp;&nbsp;If anyone can get their hands on that text and post it as a response, it would be greatly appreciated.<br><br>Thanks in advance!!!<br>Pondo<br>

 

[harryrich]

I don't know much about the SQL server specifics but I've had problems in other systems where the issue was the order in which the joins were evaluated.&nbsp;&nbsp;As in your case, the error came up a run time.&nbsp;&nbsp;The solution was to put the inner joins in a separate view and then make an outer join with the view.&nbsp;&nbsp;I assume a subquery would work equally well if this approach solves the problem.<br><br>Best,<br><br>Harry Rich

 

[harryrich]

I just started reading my text on Transact SQL.&nbsp;&nbsp;It pointed out that if you use the legacy syntax (specifying the joined in a WHERE clause) you run into an ambiguity.&nbsp;&nbsp;The terms in the WHERE clause are assumed to be associative (per SQL specification) and subject to evaluation in any order.&nbsp;&nbsp;To avoid these ambiguities it recommended sticking to ANSI SQL-92 syntax with a separate ON clause for each join.