CODEWITHSUNDEEP
sqlselectcase
ARC
ARC

Reputation: 23

SQL SELECT a value when it exists, otherwise populate a null value, multiple times

I want to select two values that are tied to a table and if they don't exist, populate a null value. But, I only want one row for each record. If I run the query below, the CASE statements create duplicate rows. 

SELECT DISTINCT YT.TITLE, YT.DETAILS,
CASE WHEN XT.ACOLUMN = 'CATEGORY' THEN XT.BCOLUMN END AS CATEGORY,
CASE WHEN XT.ACOLUMN = 'DIVISION' THEN XT.BCOLUMN END AS DIVISION 
FROM DB.YTABLE YT
LEFT OUTER JOIN DB.XTABLE XT
ON YT.ID_NUM = XT.ID_NUM

Data I want:

TitleExample1, DetailsExample1, Category1, Division1

TitleExample2, DetailsExample2, null, Division2

Data I am getting:

TitleExample1, DetailsExample1, Category1, null

TitleExample1, DetailsExample1, null, Division1

TitleExample1, DetailsExample1, null, null

TitleExample2, DetailsExample2, null, Division2

TitleExample2, DetailsExample2, null, null

I also tried doing a join using multiple table identities for the same table: 

SELECT DISTINCT YT.TITLE, YT.DETAILS, XT1.BCOLUMN AS CATEGORY1, 
       XT2.BCOLUMN AS DIVISION 
LEFT OUTER JOIN DB.XTABLE XT1
ON YT.ID_NUM = XT.ID_NUM
LEFT OUTER JOIN DB.XTABLE XT2
ON YT.ID_NUM = XT.ID_NUM
 WHERE XT1.ACOLUMN = 'CATEGORY' AND
 XT2.ACOLUMN = 'DIVISION'

This works well if both category and division values are populated. But, if a title and details record does not have an associated category or division, the query will not select the record.

Data I want:

TitleExample1, DetailsExample1, Category1, Division1

TitleExample2, DetailsExample2, null, Division2

Data I am getting:

TitleExample1, DetailsExample1, Category1, Division1

Upvotes: 0

Views: 1700

Answers (2)

Nagasaki
Nagasaki

Reputation: 58

your second approach is better, but you must add your condition in the ON statement of the OUTER JOIN

SELECT DISTINCT YT.TITLE, YT.DETAILS, XT1.BCOLUMN AS CATEGORY1, XT2.BCOLUMN AS DIVISION 
FROM DB.YTABLE YT
LEFT OUTER JOIN DB.XTABLE XT1 ON (YT.ID_NUM = XT1.ID_NUM AND XT1.ACOLUMN = 'CATEGORY')
LEFT OUTER JOIN DB.XTABLE XT2 ON (YT.ID_NUM = XT2.ID_NUM AND XT2.ACOLUMN = 'DIVISION')

Upvotes: 1

George Mastros
George Mastros

Reputation: 24506

SELECT YT.TITLE, YT.DETAILS,
Min(CASE WHEN XT.ACOLUMN = 'CATEGORY' THEN XT.BCOLUMN END) AS CATEGORY,
Min(CASE WHEN XT.ACOLUMN = 'DIVISION' THEN XT.BCOLUMN END) AS DIVISION 
FROM DB.YTABLE YT
LEFT OUTER JOIN DB.XTABLE XT
ON YT.ID_NUM = XT.ID_NUM
Group By YT.TITLE, YT.DETAILS

Upvotes: 0

Related Questions