Re: [URGENT] Conditional Outer Join (again)

It appears that this will solve my problem. If anyone sees a flaw or a way to improve this, please let me know. Thanks!

select i.firstname,i.lastname,spr.*
from esp_individ_d i, esp_sprating_d spr where i.id = spr.id
  and i.dept like 'HN5B%'
  and spr.espdate = (select max(espdate) from esp_sprating_d r where i.id=r.id )
union
select i.firstname,i.lastname,spr.*
from esp_individ_d i, esp_sprating_d spr where i.id = spr.id (+)
  and i.dept like 'HN5B%'
  and not exists
  (
  select 1
  from esp_sprating_d spr
  where i.id = spr.id
  )

In article <8n8vcp$ufl$1_at_nnrp1.deja.com>,   GF <GAYLEN.FRALEY_at_MAIL.SPRINT.COM> wrote:
> I really apprectiate the many responses I received. I have to
> apologize that by trying to be concise in my original posting, I
 wasn't
> explaining it quite right. Here is the code that extracts the unique
> record from table SPR that may or may not contain a matching record in
> table I. Table SPR can have many records per ID. Table I can only
> have 1 record per ID.
>
> select i.firstname,i.lastname,spr.*
> from esp_individ_d i, esp_sprating_d spr
> where i.id = spr.id
> and i.dept like 'HN5B%'
> and espdate = (select max(espdate) from esp_sprating_d r where
> i.id=r.id )
>
> What I now need is the code (union, outer join) for all records that
> match table I criteria and have NO records in table SPR. It seems
> simple enough, but no matter how I have tried the unions with not
> exists, it either goes into never never land or just returns the
> matches.
>
> In summary, I need to have a record from table I that has a match in
> table SPR or not.
>
> Thanks and if possible, please reply to my e-mail.
>
> gaylen.fraley_at_mail.sprint.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 14 2000 - 10:41:51 CDT