[URGENT] Conditional Outer Join (again)

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. Received on Mon Aug 14 2000 - 09:23:26 CDT