Query joins problem

[yaroni]

Hi all,
I have the following tables:
A1
==
HostID Name RunID
------ ---- -----
1 host1 NULL
2 host2 1
3 host3 NULL

A2
==
RunID SessionID
----- ---------
1 4
2 2

A3
==
SessionID Name
--------- ----
4 Session1
2 Session3

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).

Thanks in advance,
Yaron

[Erland Sommarskog]

(ya****@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, es****@sommarskog.se

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