From: bill@wirespring.com (Bill Gerba)
Newsgroups: comp.databases.oracle.server
Subject: Porting OUTER JOIN statements to Oracle 8i
Date: 8 Aug 2001 13:11:02 -0700
Organization: http://groups.google.com/
Lines: 31
Message-ID: <516329be.0108081211.162432f0@posting.google.com>
NNTP-Posting-Host: 208.21.59.119
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 997301462 5216 127.0.0.1 (8 Aug 2001 20:11:02 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2001 20:11:02 GMT


Hello,

I have some fairly complex queries that were developed in mySQL that I
now need to port over to an Oracle 8i database.  The queries join many
tables together, almost always with outer joins.  I've run into a
problem, though, where I can't use OR in my outer joins in Oracle.  I
need to be able to do something like this:

SELECT t1.*, t2.name, t2.id
FROM company AS t1 LEFT JOIN salesrep AS t2
ON (t2.id = t1.rep1 OR t2.id = t1.rep2)

I tried to simulate this in oracle with a subquery, but I can't seem
to get it to "refer" to the 'outer loop' so to speak.  I would like to
do something like this:

SELECT t1.* from company t1, 
	(select t2.name, t2.id from salesrep t2
	WHERE t2.id = t1.rep1 or t2.id = t1.rep2) t3,
WHERE t1=...

I was hoping that it would parse the subquery like a nested loop and
consequently return multiple values for t1 whenever a unique match in
the t2 subquery was found, but this doesn't work.

Is there any way to simulate an "OR" in an Oracle outer join without
resorting to temporary tables or "extreme" measure?

Thanks in advance,

Bill Gerba