SQL Server Querying table where parent-child relation is the PK and FK in same table

imzjd6km 于 2023-03-22 发布在其他

关注(0)|答案(2)|浏览(158)

I use SQL Server and T-SQL and have a Company table like so:
| Id | Name | ParentId | Provider |
| ------------ | ------------ | ------------ | ------------ |
| 1 | Name 1 | NULL | Provider 1 |
| 2 | Name 2 | NULL | Provider 2 |
| 3 | Name 3 | 2 | Provider 2 |
| 4 | Name 4 | 2 | Provider 2 |
| 5 | Name 5 | NULL | Provider 3 |
| 6 | Name 6 | NULL | NULL |
| 7 | Name 7 | NULL | NULL |
| 8 | Name 8 | 7 | NULL |
| 9 | Name 9 | 7 | NULL |
| 10 | Name 10 | NULL | Provider 4 |

I need to create a report displaying this:

Billing	Provider	TopLevel	Name
Provider 1	Provider 1	NULL	Name 1
Provider 2	Provider 2	Name 2	Name 2
Provider 2	Provider 2	Name 2	Name 3
Provider 2	Provider 2	Name 2	Name 4
Provider 3	Provider 3	NULL	Name 5
Name 6	NULL	NULL	Name 6
Name 7	NULL	Name 7	Name 7
Name 7	NULL	Name 7	Name 8
Name 7	NULL	Name 7	Name 9
Provider 4	Provider 4	NULL	Name 10

So I want the Billing column to show:

If Provider IS NOT NULL , use Company.Provider
If Provider IS NULL AND ParentId IS NULL , use Company.Name
If Provider IS NULL AND ParentId IS NOT NULL , use parent Company.Name

TopLevel

If TopLevel is NULL, value should be NULL
If TopLevel is not NULL, use parent Company.Name for both parent and child

At this point I am very stuck and sort of lost on where to go due to trying various ways to solve it which have just made things very confusing and chaotic in my mind.

I have tried left joining company table on itself, but that doesn't work since I don't get results in the same column.

I've also tried to use Case to get the values right.

The main problem is getting the TopLevel to also have the name of the parent for the parent as well when it's a parent-child relationship since the ParentID is a FK to the Id which is PK in the same table.

I've tried to look into CTE, but I am not sure if that's the correct way. I am also not familiar with CTE. Is Pivot what I should look into instead?

I don't mind being pointed to the correct material to read up on how to solve this, so I can learn more.

Any help would be appreciated at this point. Both a possible solution or tips on what to read up on to solve it.

I am not pressed for time on this as I solved in a different way which gave the person who needed the report what they needed, but I'd like to learn how to attack problems like these for the future when what I did might not work next time and just to know how to do it to get the result as I wanted.

Here is what I ended up doing to create a report that could be used, but isn't how I wanted it to be:

SELECT
    CASE
        WHEN companyAlias.Provider IS NULL 
            THEN ''
            ELSE companyAlias.Provider
    END AS "Provider",     
    CASE
        WHEN (SELECT name FROM Companies 
              WHERE Id = companyAlias.ParentId) IS NOT NULL 
            THEN (SELECT name FROM Companies 
                  WHERE Id = companyAlias.ParentId)
            ELSE companyAlias.name
    END AS "BillingCompany",
    companyAlias.name,
FROM
    Companies companyAlias

sql-server

来源：https://stackoverflow.com/questions/75782588/querying-table-where-parent-child-relation-is-the-pk-and-fk-in-same-table

2条答案

按热度按时间

f0brbegy1#

You can LEFT JOIN companies again and then check with COALESCE the names or provider

SELECT
COALESCE(c1.[Provider],c2.[Name],c1.[name]) as billing,
  COALESCE(c1.[Provider],'') as Provider,
  COALESCE(c2.name,(SELECT TOP 1 c1.name FROM Companies c3 WHERE c3.[ParentId] = c1.[ID])) toplevel,
  COALESCE(c2.[Name],c1.[Name]) Name1,
  c1.Name
FROM Companies  c1 LEFT JOIN Companies  c2 ON c1.[ParentId] = c2.[Id]

billing	Provider	toplevel	Name1	Name
Provider 1	Provider 1	null	Name 1	Name 1
Provider 2	Provider 2	Name 2	Name 2	Name 2
Provider 2	Provider 2	Name 2	Name 2	Name 3
Provider 2	Provider 2	Name 2	Name 2	Name 4
Provider 3	Provider 3	null	Name 5	Name 5
Name 6		null	Name 6	Name 6
Name 7		Name 7	Name 7	Name 7
Name 7		Name 7	Name 7	Name 8
Name 7		Name 7	Name 7	Name 9
Provider 4	Provider 4	null	Name 10	Name 10

fiddle

赞(0）回复(0）举报 2023-03-22

c90pui9n2#

you can simply use COALESCE for these rules:

If Provider IS NOT NULL, use Company.Provider
If Provider IS NULL AND ParentId IS NULL, use Company.Name
If Provider IS NULL AND ParentId IS NOT NULL, use parent Company.Name

SELECT
COALESCE(Company.Provider, Company.ParentID, Company.Name) AS yourColumn
FROM  Companies

Coalesce will always take first non-null value, so the order is important!

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Querying table where parent-child relation is the PK and FK in same table

2条答案

相关问题

热门标签

最新问答