Joining multiple result sets together

Hi All,
I've been trying this for over a week now, and I simply cannot get this to work. Basically, what I am trying to do is get a result set that has the 4 columns, PID, LAST, SMOKER, HYPER for every person in the table (every person has a unique PID). My problem is that these columns are from different tables, so I have been trying to do 2 left joins to make the result set. This is what I came up with, but it has syntax problems from what I can gather.

SELECT E.PID, E.LAST, M.SMOKER, C.HYPER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

After looking at the above query, I got the feeling that the C.HYPER might have been the problem as it is getting its data from the first left join. I tried to fix that with the statement below. It's probably terribly wrong too, but my SQL experience is limited, and i've never actually had to do a join from 3 tables before.

SELECT TT.PID, TT.LAST, TT.SMOKER_PAST, C.HYPER FROM (SELECT E.PID, E.LAST, M.SMOKER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT) LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

What I was aiming for here was to make the second select result set act like a table of its own (TT). This didn't work too well either :)

Any help at all would be greatly appreciated.

Thanks,
Michael

BTW this forum is great. I only just found it, but I think I'll be coming here a lot more now. The amount of knowledge in here is unbelievable.try this --SELECT E.PID, E.LAST, M.SMOKER, C.HYPER
FROM ENROLLMENT E
LEFT
JOIN MEDICALHISTORY M
ON E.PID = M.PID
LEFT
JOIN CARDIACHISTORY C
ON E.PID = C.PIDrudy
http://r937.com/|||Thanks for the reply. I don't have access to the database right now, but I'll get back to you on Monday to let you know how it worked.|||Hi Rudy,
Thanks for the help. I just tried it out on the database, and whilst it didn't work for me, it got me thinking and I managed to get something that was workable. I'd post up what I ended up using, but it's a mess and hardly the most efficient way of doing it (it had to be generated by code on the fly, so the actual solution I ended up with is really redundant, but is easy to build in the code when certain buttons are pressed).

Thanks for the help, mate.

Cheers,
Michael