Skip to content
Advertisement

Conditional self join SQL Server

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

sample table

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement