August 9, 2011 at 1:30 pm
Hello. I am having issues with some SQL code. I have a one to many relationship between two tables: PatientProfile and PatientContacts respectively. One patient can have multiple contacts but I only want to return one contact, and if there are multiple contacts then the "husband" or "spouse" takes precedence. I've found some SQL examples on the Internet (thanks Rick O at HERE) about how to do this so I've modified them to make them fit my situation. Here is what I have so far:
SELECT
pp.PatientId,
COALESCE(pc.[First] + ' ' + pc.[Last] + ' ('+ UPPER(pc.ContactRelToPatient) + ')','') AS [Emergency Contact]
FROM
dbo.PatientProfile pp
LEFT OUTER JOIN
(
SELECT
PatientProfileId,
COALESCE(
MIN(CASE WHEN UPPER(ContactRelToPatient) = 'HUSBAND' or UPPER(ContactRelToPatient) = 'SPOUSE' THEN PatientContactsID END),
MIN(PatientContactsID)
)AS EContact
FROM
dbo.PatientContacts
GROUP BY
PatientProfileId
) AS pc2
ON
(pp.PatientProfileId = pc2.PatientProfileId)
LEFT OUTER JOIN
dbo.PatientContacts AS pc
ON
(pc2.PatientProfileId = pc.PatientProfileId)
ORDER BY
pp.PatientID DESC
At this point, if a patient has more than one contact, both contacts are being returned. If the above code runs and I filter by "WHERE pp.PatientID = 8144" I get:
| PatientId | Emergency Contact |
| 8144| John Doe (SPOUSE) |
| 8144 | Jane Doe (DAUGHTER) |
I've isolated and run the "second" SELECT statement and it does return only a single record like it's supposed to so now I'm thinking the error must be in my third line of code:
I have:
COALESCE(pc.[First] + ' ' + pc.[Last] + ' ('+ UPPER(pc.ContactRelToPatient) + ')','') AS [Emergency Contact]
I must be using the COALESCE function incorrectly? It's supposed to be returning only one, the first, record. Or is there a JOIN incorrectly being used?
Any help?
Thanks.
AWS
August 9, 2011 at 1:42 pm
Your understanding of coalesce is incorrect. It is not going to return the first record. It is going to return the first non-null value of the list you provide it.
What you are trying to accomplish is fairly straight forward. If you want tested code please provide ddl and sample data. See the link in my signature for best practices.
_______________________________________________________________
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/
August 9, 2011 at 1:43 pm
I've managed to find one error. On my "second" ON (join section) I had:
ON
(pc2.PatientProfileId = pc.PatientProfileId)
and it needs to be:
ON
(pc2.EContact = pc.PatientProfileId)
which now yields:
PatientId | Emergency Contact
8144 | <null>
August 9, 2011 at 1:45 pm
Thanks Sean for the heads up. I'll follow through with the link in your sig for finding more answers.
EDIT:
PatientProfile table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#PatientProfile','U') IS NOT NULL
DROP TABLE #PatientProfile
--===== Create the test table with
CREATE TABLE #PatientProfile
(
PatientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #PatientProfile ON
--===== Insert the test data into the test table
INSERT INTO #PatientProfile
(PatientID)
SELECT '4' UNION ALL
SELECT '37' UNION ALL
SELECT '44' UNION ALL
SELECT '54' UNION ALL
SELECT '55' UNION ALL
SELECT '8144' UNION ALL
SELECT '86' UNION ALL
SELECT '96'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #PatientProfile ON
PatientContacts table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#PatientContacts','U') IS NOT NULL
DROP TABLE #PatientContacts
--===== Create the test table with
CREATE TABLE #PatientContacts
(
PatientContactsIDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientProfileIDINT,
[Last]VARCHAR(50),
[First]VARCHAR(50),
ContactRelToPatientVARCHAR(10)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #PatientContacts ON
--===== Insert the test data into the test table
INSERT INTO #PatientContacts
(PatientContactsID,PatientProfileID,[First],[Last],ContactRelToPatient)
SELECT '1','4','Adam','Smith','Husband' UNION ALL
SELECT '2','37','Susan','Johnson','sister' UNION ALL
SELECT '33','37','Rick','Anderson','Husband' UNION ALL
SELECT '56','44','Kelly','Adams','Friend' UNION ALL
SELECT '57','8144','John','Doe','Husband' UNION ALL
SELECT '58','8144','Jane','Doe','Daughter' UNION ALL
SELECT '59','86','Leslie','Yapp','Partner' UNION ALL
SELECT '60','96','Janice','Cooper','Mother'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #PatientContacts ON
I believe that will get the basic idea across. Please, let me know if I didn't do something right. Thanks again for the info.
AWS
August 9, 2011 at 2:41 pm
Alrighty, got it figured out.
Here is the final code:
SELECT
pp.PatientProfileID,
COALESCE(pc.[First] + ' ' + pc.[Last] + ' ('+ UPPER(pc.ContactRelToPatient) + ')','') AS [Emergency Contact]
FROM
dbo.PatientProfile pp
LEFT OUTER JOIN
(
SELECT
PatientProfileId,
COALESCE(
MIN(CASE WHEN UPPER(ContactRelToPatient) = 'HUSBAND' or UPPER(ContactRelToPatient) = 'SPOUSE' THEN PatientContactsID END),
MIN(PatientContactsID)
)AS EContact
FROM
dbo.PatientContacts
GROUP BY
PatientProfileId
) AS pc2
ON
(pp.PatientProfileId = pc2.PatientProfileId)
LEFT OUTER JOIN
dbo.PatientContacts AS pc
ON
(pc2.EContact = pc.PatientContactsId)
WHERE pp.PatientProfileID = 8144
ORDER BY
pp.PatientID DESC
I just had to draw it out on paper to see it then I could see I was linking incorrectly.
Thanks for looking/helping.
AWS
August 9, 2011 at 3:05 pm
Sometimes it is just the act of looking the data and structure to explain it to somebody else that reveals the answer. Glad you figured it and thanks for letting us know.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply