| Using outer join with a subquery [message #20046] |
Fri, 26 April 2002 06:19  |
Matthew
Messages: 20
Registered: July 2001
|
Junior Member |
|
|
I want to use an outer join to select all customers, and if there are linked records on company_accounts to take the most recent record (as determined by date_posted). Problem is that the outer join has no effect when the subquery is added to the WHERE. Please help, thanks.
select columna, columnb, etc
from customer c, company_accounts ca
where c.company_id = ca.company_id (+)
and ca.date_posted = (select max(ca2.date_posted)
from company_accounts ca2
where ca2.company_id = c.company_id
group by ca2.company_id)
|
|
|
|
| Re: Using outer join with a subquery [message #20060 is a reply to message #20046] |
Fri, 26 April 2002 21:14  |
Saga
Messages: 51
Registered: April 2002
|
Member |
|
|
The problem is u r using correlatd query instead of subquery. A correlated query fires for every row of data fetched by the main query. So the condition in correlated query overrides the outer join in ur case.
Hope it helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
