SEO

#1 (**permalink**) 02-29-2008, 05:53 AM

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

#2 (**permalink**) 02-29-2008, 05:53 AM

(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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Query joins problem