One table that is related to another table twice

[arnyinc]

I'm use ASP and SQL to query an oracle 8i database. I have a table with a list of projects. Two of the fields in this table are proj_primary and proj_backup. These are the primary and secondary contact people for the project. I then have a Contacts table with a corresponding ID. I use a left outer join to find the contact information in the contacts table:

select p.proj_primary, p.proj_backup, c.contact_fname, c.contact_lname from projects p, contacts c WHERE p.proj_primary=c.contact_id(+);

Is it possible to get the primary and backup contact information in one SQL statement? This only gives me the primary contact's information.

[Roy Sinclair]

For MS SQL I would code it something like below, it should be the same or very similar for Oracle.


select
p.proj_primary
,p.proj_backup
,c1.contact_fname
,c1.contact_lname
,c2.contact_fname as backup_fname
,c2/contact_lname as backup_lname
from projects p
left join contacts c1 on p.proj_primary=c1.contact_id
left join contacts c2 on p.proj_backup=c2.contact_id

[arnyinc]

Thanks for the help!

As it turns out, I couldn't find any example that showed oracle supporting standard ANSI joins. I probably should have already known that.

Anyways, I took the your idea of using c1 and c2 to use the contacts table twice. Again, something I probably should have known. :o

I ended up with this if anyone is interested (I changed the field names a little).

select c1.contact_fname, c2.contact_fname from offtime o, contacts c1, contacts c2 WHERE o.off_person=c1.contact_id(+) AND o.off_backup=c2.contact_id(+);