Multiple Joins rendering incorrect data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Joins rendering incorrect data

Author

Topic

NickG21
Starting Member

6 Posts

Posted - 2012-06-19 : 14:21:05

Hey everyone,
The below query is giving me a resultset that is not correct for the parameters entered. The query is:


SELECT Inquiry.ID, Inquiry.Date, Inquiry.DecisionMade, Inquirer.Name, Inquirer.Program, Patient.Name, DecAdmit.PlanAdmitDate from Inquiry
Inner Join Inquirer on Inquiry.InquirerID_fk = Inquirer.ID
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' AND Inquirer.Program = 'Lincoln'

And the results that I am getting have the Inquirer.Program = 'Hill Center' instead of = 'Lincoln' I assume this is a syntax error that I have but I am not able to figure it out. Any help is greatly appreciated.
Thank you in advance,
NickG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-19 : 14:22:49

You'll need parentheses

Where (DecAdmit.ActualAdmitDate > Convert(Varchar(10), GetDate(), 120) or DecAdmit.ActualAdmitDate is NULL)
And Inquiry.DecisionMade = 'Decision to admit' AND Inquirer.Program = 'Lincoln'

Jim

Everyday I learn something that somebody else already knew

NickG21
Starting Member

6 Posts

Posted - 2012-06-19 : 14:25:15

That makes me feel real ridiculous lol, thank you for the most obvious answer that I couldn't think of. I really appreciate it a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 14:25:31

why do you need to convert date to varchar? why not simply?

DecAdmit.ActualAdmitDate>= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources