CODEWITHSUNDEEP
sqlsql-server
Nick
Nick

Reputation: 7525

Joining to the same table in SQL - SQL Server 2008

I have the following table:

ID  Type  Description IDOfSystem
--------------------------------
1000 Company  Company Item   NULL
1010 System   System Item    NULL
1020 Company  NULL           1010

I have System and Company Items. I need to write a select query that gets all the company items and system items UNLESS if a company item has a value in IDOfSystem I need to exclude that system item and get the description from the system item.

So, given the above table, the SQL select should return rows 1000, 1020 (with "System Item") as the description.

If 1020 didn't exist, I'd simply get 1000 and 1010.

I guess I can break this up into multiple queries and do a UNION. I tried to do a left outer join on the same table but couldn't get the description from the system row.

Any help?

Upvotes: 2

Views: 966

Answers (4)

Andomar
Andomar

Reputation: 238048

This approach uses a self join to look up the corresponding system's description. A separate subquery filters out the referenced systems.

select  yt1.Id
,       yt1.Type
,       coalesce(yt2.Description, yt1.Description) as Description
from    YourTable yt1
left join
        YourTable yt2
on      yt1.type = 'Company'
        and yt2.type = 'System'
        and yt2.ID = yt1.IDOfSystem
where   yt1.type in ('System', 'Company')
        and not exists 
        (
        select  *
        from    YourTable yt3
        where   yt1.type = 'System'
                and yt3.type = 'Company'
                and yt1.ID = yt3.IDOfSystem
        )

Working example at SE Data.

Upvotes: 0

kevev22
kevev22

Reputation: 3775

SELECT 
     Companies.ID
    ,Companies.Type
    ,COALESCE(Systems.Description, Companies.Description) as Description
FROM YourTable Companies
LEFT OUTER JOIN YourTable Systems on Systems.ID = Companies.IDOfSystem
WHERE NOT EXISTS
(
    SELECT * FROM YourTable T3 WHERE T3.IDOfSystem = Companies.ID
)

Here it is running on SEDE.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

I'm sure there are better ways of doing this, but try:

SELECT A.Id, A.Type, ISNULL(A.Description,B.Description) Description, A.IDOfsystem
FROM YourTable A
LEFT JOIN YourTable B
ON A.IDOFSystem = B.ID
WHERE A.ID NOT IN (SELECT IDOfsystem FROM YourTable WHERE IDOfsystem IS NOT NULL)

Upvotes: 0

Dylan Smith
Dylan Smith

Reputation: 22235

SELECT ID, Type, Description
FROM MyTable AS A
WHERE IDOfSystem IS NULL AND NOT EXISTS (SELECT * 
                                         FROM MyTable AS B 
                                         WHERE B.IDOfSystem = A.ID)
UNION ALL
SELECT A.ID, A.Type, B.Description 
FROM MyTable AS A INNER JOIN MyTable AS B ON A.IDOfSystem = B.ID
WHERE IDOfSystem IS NOT NULL

What I'm doing is first selecting all rows that don't have a referenced system, and aren't used as some other rows system.

Then I'm doing a union with another query that finds all rows with a referenced system, and joining in the system to grab it's description.

Upvotes: 1

Related Questions