December 1, 2015 at 12:11 pm
Hello All,
I'm not sure if I just have not had enough coffee today or I am truly missing something larger.
I am trying to pull those patients who have had BOTH a Potassium test and a Calcium test. However I am not getting my intended results and I am kind of embarrassed that I don't understand why. I am sure it is something simple that I am missing. I have provided some sample data along with the logic I'm confused about.
Any help or guidance is much appreciated!
CREATE TABLE #Patient (PatientID INT, PatientName VARCHAR (10), TESTID INT)
INSERT #Patient VALUES (101,'John',1), (102,'Mike',1),(103,'Bob',1),(104,'Tim',1)
,(101,'John',2), (102,'Mike',2),(103,'Bob',2),(104,'Tim',2)
CREATE TABLE #Test (TestID INT, TestName VARCHAR(10))
INSERT #Test VALUES (1,'Potasium'), (2,'Calcium')
/*Results 4 Patients*/
SELECT *
FROM #Patient P
INNER JOIN #Test pot ON pot.TestID = p.TESTID
WHERE pot.TestName ='Potasium'
/*Results 4 Patients*/
SELECT *
FROM #Patient P
INNER JOIN #Test Cal ON Cal.TestID = p.TESTID
WHERE cal.TestName = 'Calcium'
/*Expected Results 4 Patients 0 Returned, Why ??*/
SELECT *
FROM #Patient P
INNER JOIN #Test pot ON pot.TestID = p.TESTID
INNER JOIN #Test Cal ON Cal.TestID = p.TESTID
WHERE pot.TestName ='Potasium' AND cal.TestName = 'Calcium'
DROP TABLE #Patient,#Test
***SQL born on date Spring 2013:-)
December 1, 2015 at 12:24 pm
Let's look at your last query. Reworking this a little bit to make it easier to see it would be something like this.
SELECT *
FROM #Patient P
INNER JOIN #Test pot ON pot.TestID = p.TESTID and pot.TestName ='Potasium'
INNER JOIN #Test Cal ON Cal.TestID = p.TESTID and cal.TestName = 'Calcium'
There are exactly 0 rows that can have both conditions met. If you comment out the second join think about the results. You would have only the 4 rows where there is a test for 'Potasium', since you then do another inner join you saying you want the rows from the first part then add a second condition which is impossible to have.
What are you trying to get for results?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2015 at 12:26 pm
Thanks Sean,
I am trying to get all patients that had both a Potassium and a Calcium test. Which should be all 4 people.
***SQL born on date Spring 2013:-)
December 1, 2015 at 12:32 pm
The INNER JOINs to both aliased versions of #TEST are joining on equality with the TestID from #Patient, which means each row will have the same Test (either Potassium or Calcium) from both Cal and Pot.
Here are a couple ways to do it. The first is very inefficient, but I included it since it clearly shows what is going on logically.
SELECT Cal.PatientName FROM
--Get all patients with a Calcium test
(SELECT P.* FROM #Patient P INNER JOIN #Test T ON T.TestID=P.TESTID WHERE T.TestName='Calcium') Cal
INNER JOIN
--Join with all patients with a Potassium test
(SELECT P.* FROM #Patient P INNER JOIN #Test T ON T.TestID=P.TESTID WHERE T.TestName='Potasium') Pot
ON Cal.PatientID=Pot.PatientID
--More efficient method using HAVING
SELECT MAX(P.PatientName) FROM #Patient P INNER JOIN #Test T ON T.TestID=P.TESTID
GROUP BY P.PatientID
HAVING
SUM(CASE WHEN T.TestName='Potasium' THEN 1 ELSE 0 END)>0
AND
SUM(CASE WHEN T.TestName='Calcium' THEN 1 ELSE 0 END)>0
Cheers!
EDIT: I see while I was putting this together Sean already pointed out the issue with the current query, so consider mine a +1 to his 🙂
December 1, 2015 at 12:39 pm
Thanks Jacob,
Those both work. I have this just now but not sure how performance would be. Aliasing the Patient table into 2 different tables.
SELECT p.*, pot.TestName, cal.TestName
FROM #Patient P
INNER JOIN #Patient Q ON p.PatientID = q.PatientID
INNER JOIN #Test pot ON pot.TestID = p.TESTID
INNER JOIN #Test Cal ON Cal.TestID = q.TESTID
WHERE pot.TestName ='Potasium' AND cal.TestName = 'Calcium'
***SQL born on date Spring 2013:-)
December 1, 2015 at 1:00 pm
Sean Lange (12/1/2015)
Let's look at your last query. Reworking this a little bit to make it easier to see it would be something like this.
SELECT *
FROM #Patient P
INNER JOIN #Test pot ON pot.TestID = p.TESTID and pot.TestName ='Potasium'
INNER JOIN #Test Cal ON Cal.TestID = p.TESTID and cal.TestName = 'Calcium'
There are exactly 0 rows that can have both conditions met. If you comment out the second join think about the results. You would have only the 4 rows where there is a test for 'Potasium', since you then do another inner join you saying you want the rows from the first part then add a second condition which is impossible to have.
What are you trying to get for results?
Sean thanks for your help. It took some thinking but I finally understand this now. I kind of feel silly now. this had me going for a quite a bit.:hehe:
***SQL born on date Spring 2013:-)
December 1, 2015 at 1:01 pm
Jacob's examples are great. Just for grins here is another approach. This should be about the same for performance but is a bit simpler to understand in my opinion.
select p.PatientName
from #Patient P
INNER JOIN #Test T ON T.TestID = P.TESTID
group by p.PatientName
having COUNT(distinct t.TestID) = 2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply