| Problem with Outer Joins [message #373252] |
Fri, 06 April 2001 05:05  |
Nagaraju Korada
Messages: 3
Registered: April 2001
|
Junior Member |
|
|
I have a problem with outerjoins:
I am joining three tables one master table and two details tables.
I want to get the rows that are present in master not in details. so i am going
for outerjoin for the two detail tables.
i am also giving some static where conditions for the detail tables,now i don't want this static
condition to be applied for the outerjoined rows.
say for example i ave dept table(master), and i have employee table(detail 1) and i have a table bonus(detail 2)
query is:
select count(*) from
dept d, emp e, bonus b
where e.joindate > '20-mar-2000' and b.joindate > '20-mar-2000'
/* i don't want this condition to be applied to the extra rows that are coming due to outer join,
becuase for those extra rows that have come due to outerjoin,
joindate is null for the both detail tables. So the condition fails, and outer join is of no use. */
and d.empid = e.empid(+) and d.empid = b.empid(+)
|
|
|
|
| Re: Problem with Outer Joins [message #373253 is a reply to message #373252] |
Fri, 06 April 2001 06:39  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
You need to include the outer join marker (+) on all references to the outer joined tables.
That way the where clause condition should only be applied to rows that exist.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
