asked on

SQL CASE

This is driving me nuts. I have the following code:

SELECT dbo.ITX_vw_tbl_Patient.UnosId
FROM dbo.ITX_vw_tbl_Patient INNER JOIN
dbo.ITX_vw_tbl_DonorReferral ON dbo.ITX_vw_tbl_Patient.Id = dbo.ITX_vw_tbl_DonorReferral.PatientId INNER JOIN
dbo.ITX_vw_tbl_EnumerationValue ON dbo.ITX_vw_tbl_DonorReferral.OrganOutcomeDetail = dbo.ITX_vw_tbl_EnumerationValue.Id
GROUP BY dbo.ITX_vw_tbl_Patient.UnosId

It returns the UnosId. The problem is I need to return multiple records related to this UnosId from dbo.ITX_vw_tbl_EnumerationValue and list them as columns. I tried doing a CASE but it wasn't returning any values.

Guy Hengel [angelIII / a3]

please post some data you have and get, and what you should get.

I presume you need the GROUP BY, and a MAX() around your case statement(s) .

LCNW

ASKER

This code:

SELECT dbo.ITX_vw_tbl_Patient.UnosId, MAX(CASE WHEN EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN Description END) AS 'Race',
MAX(CASE WHEN EnumerationTypeId = 'bbe49dfa-36c4-4dc4-ab24-161b66111709' THEN Description END) AS Outcome
FROM dbo.ITX_vw_tbl_Patient INNER JOIN
dbo.ITX_vw_tbl_DonorReferral ON dbo.ITX_vw_tbl_Patient.Id = dbo.ITX_vw_tbl_DonorReferral.PatientId INNER JOIN
dbo.ITX_vw_tbl_EnumerationValue ON dbo.ITX_vw_tbl_DonorReferral.OrganOutcomeDetail = dbo.ITX_vw_tbl_EnumerationValue.Id
GROUP BY dbo.ITX_vw_tbl_Patient.UnosId

Returns:

VG1073      NULL      NULL
XFZ496      NULL      NULL
XJB371      NULL      NULL
VHB046      NULL      NULL
YGG243      NULL      NULL
ZD1218      NULL      NULL
YLY223      NULL      NULL
VKW277      NULL      NULL
ZFL099      NULL      NULL

I know those EnumerationIds exist. I can filter against the table directly and get a matches.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCNW

ASKER

How could I list these 2 Ids side-by-side?

SELECT EnumerationTypeId, Description
FROM dbo.ITX_vw_tbl_EnumerationValue
WHERE (EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0') OR
(EnumerationTypeId = 'bbe49dfa-36c4-4dc4-ab24-161b66111709')

Result is:

bbe49dfa-36c4-4dc4-ab24-161b66111709      SCD
bbe49dfa-36c4-4dc4-ab24-161b66111709      ECD
bbe49dfa-36c4-4dc4-ab24-161b66111709      DCD
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Guamanian or Chamorro
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Cuban
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Native Hawaiian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: African American
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Korean
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Asian Indian/Indian Sub-Continent
d9510b51-3677-4c39-b76a-eea698f50bc0      White: European Descent
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Arab or Middle Eastern
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Puerto Rican (Island)
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: West Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Haitian
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Mexican
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Alaska Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: African (Continental)
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Samoan
d9510b51-3677-4c39-b76a-eea698f50bc0      White: North African (non-Black)
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Chinese
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Vietnamese
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Filipino
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Puerto Rican (Mainland)
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Japanese
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Eskimo
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: American Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Aleutian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Other
NULL      NULL

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCNW

ASKER

Okay I'm going to try and explain this one more time, starting with logic. I have a PatientID table and an EnumerationValue Table. Here are their columns:

PatientID:
Id Age Sex Weight Height Race

EnumerationValue:
Id Description Value

The only way I have found to link these tables is through the Race>Value column. Problem is, I need to get additional data out of the EnumerationValue table once joined. I need some other values for the same PatientID and I'm not sure how to get those additional values as the join locks me down to race related info.