Results 1 to 7 of 7

Thread: Query help???

  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Question Unanswered: Query help???

    Hello guys,

    I have a table Hospitals (HospitalID,HospitalName),it gives the list of hospitals, and table Units where relevant data gets written once users submitt it and I have HospitalID in that table as well.

    I am trying to 'left join' these tables in order to get; not the hospitals that have submitted but the once that haven't but i am only getting results for the once that have submitted.

    How can I turn around this query to get the list of hospital that haven't submitted.

    Thanks to all

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT h.HospitalID, h.HospitalName
    FROM
    Units u
    LEFT OUTER JOIN Hospitals h ON h.HospitalID = u.HospitalID
    WHERE
    u.HospitalID IS NULL
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you want your LEFT JOIN to point the other way:

    select distinct HospitalID, HospitalName
    from Hospitals
    left outer join Units on Hospitals.HospitalID = Units.HospitalID
    where Units.HospitalID is null

    This returns the Hospitals with no matching record in the Units table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    not the hospitals that have submitted but the once that haven't

    Isn't that what he wanted returned blindman? I'm probably needing some sleep right now.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Ihave tried all of this that's why I don't understand....

    "select distinct HospitalID, HospitalName
    from Hospitals
    left outer join Units on Hospitals.HospitalID = Units.HospitalID
    where Units.HospitalID is null"
    this returns "ambigous column name HospitalID"


    and this one

    "
    SELECT h.HospitalID, h.HospitalName
    FROM
    Units u
    LEFT OUTER JOIN Hospitals h ON h.HospitalID = u.HospitalID
    WHERE
    u.HospitalID IS NULL"

    returns two column names with no data....

    What am I doing wrong....

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Hospitals with no matching record in the Units table" = "Hospitals that haven't submitted".

    Get some rest, Derrick! Tomorrow is Monday...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Ignore me, I am not quite myself today......
    It is working fine,I am getting what I am after,I am crazy downunder....

    Thanks
    Last edited by zobernjik; 05-24-04 at 00:41.

Posting Permissions

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