SQL ?

From: Eric Worthy <admin_at_t10NOSPAM.net>
Date: Mon, 10 Dec 2001 14:27:07 -0700
Message-ID: <3C1528AB.83A09048@t10NOSPAM.net>

3 tables:
Table Employee

---

Columns: Id number, Site number, Name varchar2(50)

Table Salary

---

Columns: Sal_ID number, Sal_Site number, Pay_Date date

Table Salary_Hist

---

Columns: Sal_h_ID number, Sal_h_Site number, Pay_Date date

I want to select : ID, SITE, Name, Pay_Date from the following tables. BUT only if
the Pay_Date exists in either the Salary or the Salary_Hist table.

Example Data:
Employee:

1    500      'Bob Jones'
2    500      'Timmay!'
3    502        'Phil Smith'
4    580        'John Paul'

Salary
1 500 17-NOV-2001
4 502 19-DEC-2001 Salary_Hist

3    502    17-NOV-2001
4    580    17-NOV-2001
6    523    28-NOV-2001

I want my query to bring back the following when my Pay_Date = 17-NOV-2001

1    500    'Bob Jones'    17-NOV-2001
3    502    'Phil Smith'    17-NOV-2001
4    580    'John Paul'    17-NOV-2001

Select e.id, e.site, pay_date
from
employee e,
salary s,
salary_hist sh
where
(e.id = s.id and e.site = s.site)

and
(e.id = sh.id and e.site = sh.site)

This doesn't work...

How can I first go look at the Salary table to see if the data is there, and if not, go to the Salary_Hist table
and see if it's there?

Thanks,
Eric Received on Mon Dec 10 2001 - 15:27:07 CST