You need to use OUTER JOINs to accomplish what you want:

	SELECT E.EmpID, E.EmpName, D.Days, H.Hours, P.Pay
		FROM Employees E
			LEFT JOIN Days D ON E.EmpID = D.EmpID
			LEFT JOIN Hours H on E.EmpID = H.EmpID
			JOIN Pay P on E.EmpID = P.PayID
	WHERE P.PayPeriod = 1;

Note that 'missing' columns (e.g. entries in the 'Days' table for EmpID
values 4 and 5) will be set to NULL.

I left the join to the 'Pay' table as an inner join to only pick up those
Employees who get paid at all...

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Arbon Reimer [mailto:arbon_reimer@h...]
Sent: Friday, September 13, 2002 8:10 PM
To: sql language
Subject: [sql_language] Can't figure out the join...


I have four tables

Employees
EmpName       EmpID
======================
John             1
Mary             2
Jeff             3
Jim              4
Margaret         5

Days
EmpID          Days
======================
1               10
2               20
3               30

Hours
EmpID           Hours
=====================
4                40
5                50

Pay
EmpID    PayPeriod           Pay
================================
1           1              100.00
2           1              200.00
3           1              300.00
4           1              400.00
5           1              500.00

Here's my dilemma... I want to get a set of records that select the
Employee Name and ID, with hours if they have any, days if they have any,
and pay.

SELECT E.EmpID, E.EmpName, D.Days, H.Hours, P.Pay
FROM Employees E
JOIN Days D ON E.EmpID = D.EmpID
JOIN Hours H on E.EmpID = H.EmpID
JOIN Pay P on E.EmpID = P.PayID
WHERE P.PayPeriod = 1

Unfortunately I don't get any records.  I can join the Pay table without
the Days or Hours table and I get records, just without the hours or days
that each employee worked.  Can someone help me figure out exactly which
join I need to use to get the records of all employees?

Thanks for your help everyone!
Arbon Reimer
Golden, Colorado