SELECT
	t1.P_ID,
	t1.FirstName,
	t1.Lastname,
	t2.P_ID,
	t2.DOS_PID,
	t3.DOS_ID,
	t3.test,
	t3.test_date,
	t4.DOS_ID,
	t4.Payment,
	t4.pay_date
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON t1.P_ID=t2.P_ID
	LEFT JOIN Table3 t3 ON t3.DOS_ID=t2.DOS_ID
	LEFT JOIN Table4 t4 ON t4.DOS_ID = t2.DOS_ID;

quote:

---

Originally posted by alain-janquart

Every P_ID can have multiple DOS_ID's.
But one DOS_ID has only one record in table 3 and table 4.

What I have now is a list with following fields :
DOS_ID, test_date, pay_date, difference between those 2 days.

And I want :
Lastname, Firstname, DOS_ID, test_date, pay_date, difference between those 2 days.

Thanks

Lainkes

---

SELECT
	t1.Lastname,
	t1.FirstName,
	t2.DOS_PID,
	t3.test_date,
	t4.pay_date,
	DATEDIFF(dd,t3.test_date, t4.pay_date) AS Days
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON t1.P_ID=t2.P_ID
	INNER JOIN Table3 t3 ON t3.DOS_ID=t2.DOS_ID
	INNER JOIN Table4 t4 ON t4.DOS_ID = t2.DOS_ID;

quote:

---

Originally posted by alain-janquart

I want to view all records, even if there are different ones for a P_ID

If P_ID with value 50 and name 'John Doe' has 3 DOS_ID's, then I want 3 rows like :
Doe, John, 50, test_date, pay_date, difference between those 2 days.
Doe, John, 50, test_date, pay_date, difference between those 2 days.
Doe, John, 50, test_date, pay_date, difference between those 2 days.

I hope I explain correctly what i want.

Greetings

Lainkes

---

SELECT
	t1.P_ID,
	t1.FirstName,
	t1.Lastname,
	t2.P_ID,
	t2.DOS_PID,
	t3.DOS_ID,
	t3.test,
	t3.test_date,
	t4.DOS_ID,
	t4.Payment,
	t4.pay_date,
        DATEDIFF(t3.test_date,t4.pay_date) AS TimeDiff
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON t1.P_ID=t2.P_ID
	LEFT JOIN Table3 t3 ON t3.DOS_ID=t2.DOS_ID
	LEFT JOIN Table4 t4 ON t4.DOS_ID = t2.DOS_ID;