locked
Multiple Joins not working correctly RRS feed

  • Question

  • User1766347310 posted

    Hey everyone,

       I just posted a thread with this same information but I am unable to view it so i assume other people can't either.  I have the following query and it is giving me a bad resultset and i assume it has to do with my syntax but can't figure out where i have gone wrong. The query is as follows:

    SELECT Inquiry.ID as InquiryID, Inquiry.Date, Inquiry.DecisionMade, Inquirer.Name, Inquirer.Program, DecAdmit.ID, Patient.Name, DecAdmit.PlanAdmitDate, DecAdmit.InquiryID_fk from Inquirer 
    Inner Join (Inquiry INNER JOIN Patient on Patient.ID = Inquiry.PatientID_fk Inner Join DecAdmit on DecAdmit.InquiryID_fk = Inquiry.ID)
    on Inquirer.ID = Inquiry.InquirerID_fk
    Where DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120) or DecAdmit.ActualAdmitDate is NULL And Inquiry.DecisionMade = 'Decision to admit'
    AND Inquirer.Program = 'Lincoln'

    the problem is that it is displaying results where the Inquirer.Program = 'Hill Center' and not 'Lincoln'

    any help into this is much appreciated.

    Thanks for your time,

    NickG

    Tuesday, June 19, 2012 1:47 PM

Answers

  • User-1142685999 posted

    try this:

    SELECT Inquiry.ID as InquiryID,
      Inquiry.Date,
      Inquiry.DecisionMade, 
      Inquirer.Name, 
      Inquirer.Program, 
      DecAdmit.ID, 
      Patient.Name, 
      DecAdmit.PlanAdmitDate, 
      DecAdmit.InquiryID_fk 
     
     from Inquirer
      Inner Join Inquiry on Inquirer.ID = Inquiry.InquirerID_fk AND Inquirer.Program = 'Lincoln'
      INNER JOIN Patient on Patient.ID = Inquiry.PatientID_fk 
      Inner Join DecAdmit on DecAdmit.InquiryID_fk = Inquiry.ID
     
     Where DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120) 
      or DecAdmit.ActualAdmitDate is NULL 
      And Inquiry.DecisionMade = 'Decision to admit'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 20, 2012 12:38 AM
  • User1999579388 posted

    Hi NickG21,

    You could modify like below query.

    SELECT Inquiry.ID as InquiryID
    , Inquiry.Date
    , Inquiry.DecisionMade
    , Inquirer.Name
    , Inquirer.Program
    , DecAdmit.ID
    , Patient.Name
    , DecAdmit.PlanAdmitDate
    , DecAdmit.InquiryID_fk 
    from Inquirer Inner Join Inquiry on Inquirer.ID = Inquiry.InquirerID_fk 
    INNER JOIN Patient on Patient.ID = Inquiry.PatientID_fk 
    Inner Join DecAdmit on DecAdmit.InquiryID_fk = Inquiry.ID
    
    Where Inquiry.DecisionMade = 'Decision to admit' 
    AND Inquirer.Program = 'Lincoln'
    AND (DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120)
    or DecAdmit.ActualAdmitDate is NULL)

    SQL AND & OR Operators: http://www.w3schools.com/sql/sql_and_or.asp

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2012 6:03 AM

All replies

  • User-1590642642 posted

    With your current syntax, the query will return all rows where DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120), ignoring the rest of the WHERE clause.

    When you mix OR/AND in your WHERE clause, you must use parenthesis very carefully.

    Tuesday, June 19, 2012 3:53 PM
  • User-1142685999 posted

    try this:

    SELECT Inquiry.ID as InquiryID,
      Inquiry.Date,
      Inquiry.DecisionMade, 
      Inquirer.Name, 
      Inquirer.Program, 
      DecAdmit.ID, 
      Patient.Name, 
      DecAdmit.PlanAdmitDate, 
      DecAdmit.InquiryID_fk 
     
     from Inquirer
      Inner Join Inquiry on Inquirer.ID = Inquiry.InquirerID_fk AND Inquirer.Program = 'Lincoln'
      INNER JOIN Patient on Patient.ID = Inquiry.PatientID_fk 
      Inner Join DecAdmit on DecAdmit.InquiryID_fk = Inquiry.ID
     
     Where DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120) 
      or DecAdmit.ActualAdmitDate is NULL 
      And Inquiry.DecisionMade = 'Decision to admit'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 20, 2012 12:38 AM
  • User1999579388 posted

    Hi NickG21,

    You could modify like below query.

    SELECT Inquiry.ID as InquiryID
    , Inquiry.Date
    , Inquiry.DecisionMade
    , Inquirer.Name
    , Inquirer.Program
    , DecAdmit.ID
    , Patient.Name
    , DecAdmit.PlanAdmitDate
    , DecAdmit.InquiryID_fk 
    from Inquirer Inner Join Inquiry on Inquirer.ID = Inquiry.InquirerID_fk 
    INNER JOIN Patient on Patient.ID = Inquiry.PatientID_fk 
    Inner Join DecAdmit on DecAdmit.InquiryID_fk = Inquiry.ID
    
    Where Inquiry.DecisionMade = 'Decision to admit' 
    AND Inquirer.Program = 'Lincoln'
    AND (DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120)
    or DecAdmit.ActualAdmitDate is NULL)

    SQL AND & OR Operators: http://www.w3schools.com/sql/sql_and_or.asp

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2012 6:03 AM