By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Query joins problem

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.