(yaroni@gmail.com) writes:
> I want to show every record from A1 with SessionName from A3, unless
> the field RunID in A1 is NULL and then I want to see NULL, like this:
> HostID Name RunID Name
> ------ ---- ----- ----
> 1 host1 NULL NULL
> 2 host2 1 Session1
> 3 host3 NULL NULL
>
>
> when I try the following query:
>
> select A1.*, A3.Name
> from A1, A2, A3
> where A1.RunId *= A2.RunId and A2.SessionID = A3.SessionID
>
> I get the following error:
>
> The table 'A2' is an inner member of an outer-join clause. This is not
> allowed if the table also participates in a regular join clause.
>
> How can I overcome this problem. Please help. (I use this syntax istead
> of joins since I have to supprt also Oracle DB and this syntax is
> simpler to translate).

The *= is deprecated and for very good reasons of which you have run into
one. *Never* use it.

Try this:

SELECT A1.*, A3.Name
FROM A1
LEFT JOIN (A2 JOIN A3 ON A2.SessionID = A3.SesssionID)
ON A1.RunID = A2.RunID

This is per ANSI specification, and while I know next to nothing, I am
fairly sure that this works on Oracle too.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp