Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Newcastle, Australia
    Posts
    3

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    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.PID
    rudy
    http://r937.com/

  3. #3
    Join Date
    Sep 2003
    Location
    Newcastle, Australia
    Posts
    3
    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.

  4. #4
    Join Date
    Sep 2003
    Location
    Newcastle, Australia
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •