Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Red face Unanswered: Two Columns pointing to the same key ?? SQL needed

    Delete due to copyright reasons PLEASE
    Last edited by islamgamal5; 11-23-13 at 15:36.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Redesign your tables
    Patient is a separate entity to admissions.
    A single patient may have multiple admissions.
    Admissions is in effect an intersection or junction table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by islamgamal5 View Post
    I would like to develop an SQL that will show the following :
    Why? Without understanding the purpose, we can only guess at what you need and how it ought to be presented.
    Quote Originally Posted by islamgamal5 View Post
    Please keep in mind that some patients don't have a physician at all
    What do you want to do (or not do) with patients that don't have any physician?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2011
    Posts
    2
    Thank you Pat for the prompt response.
    This is needed for an assignment, so changing the tables will not be an option.
    With the patients that dont have a physician, I would like to display the prescribing doctor and the referring doctor as null.

    Here is the best thing I can come up with, but its not working for me :

    SELECT Admission.[PatientAC#], Admission.PatientFirstName, Admission.PatientLastName, Admission.AdmitDate,

    PrescribingPhysician =(
    SELECT (Physician.Firstname + Physician.LastName)
    FROM Physician LEFT JOIN Admission ON Physician.[PhysicianID] = Admission.[PrescibingPhysicianID]
    ),

    ReferringPhysician =(
    SELECT (Physician.Firstname + Physician.LastName)
    FROM Physician LEFT JOIN Admission ON Physician.[PhysicianID] = Admission.[ReferringPhysicianID]
    )
    FROM Physician LEFT JOIN Admission ON Physician.[PhysicianID] = Admission.[PrescibingPhysicianID];

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This thread has been closed pending iNet review.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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