I’m trying to self join with conditions.
Is there a way where I could do this without using Union? (union works fine but the query doubles in size which I’m trying to avoid)
Below is the query I’ve written (any suggestion or guidance would be much appreciated!)
Select concat(de.EthnicityText ,' - ', dec2.EthnicityText) as 'Ethnicity' from dl.DimEthnicity de inner join (select dec1.EthnicityParentId, dec1.EthnicityText from dl.dimethnicity dec1) as dec2 on de.EthnicityID = dec2.EthnicityParentId union all select de.EthnicityText as 'Ethnicity' from dl.DimEthnicity de where de.EthnicityParentId is null
Advertisement
Answer
Assuming you’re wanting to return all values, even if ParentID is null.
select a.EthnicityText + case when b.EthnicityText is null then '' else ' - ' + b.EthnicityText end from DimEthnicity a left join DimEthnicity b on b.EthnicityID = a.EthnicityParentID
Left join allows for the condition you’re looking for (only return the self join row when it exists). More info on left joins: https://www.w3schools.com/sql/sql_join_left.asp