How to get expected output in SQL Server

Question

I have a question about SQL Server: how to get required output based on below table data?

Table: parent

CREATE TABLE [dbo].[parent]
(
    [parentsupplierid] [int] NULL,
    [supplierid] [int] NULL,
    [suppliername] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) 
VALUES (123, 321, N'AAA'), (123, 231, N'BBB'),
       (NULL, 123, N'ABC'), (456, 654, N'DDD'),
       (NULL, 546, N'EEE'), (NULL, 456, N'DEF'),
       (789, 987, N'GGG'), (NULL, 879, N'HHH'),
       (NULL, 789, N'GHI')

based on above data I want output like below:

parentsupplierid | supplierid | suppliername | PARENTsuppliername
-----------------+------------+--------------+-----------------
123              | 231        | BBB          |  ABC
123              | 321        | AAA          |  ABC
456              | 654        | DDD          |  DEF
456              | 546        | EEE          |  DEF
789              | 987        | GGG          |  GHI
789              | 879        | HHH          |  GHI

I tried like this:

SELECT DISTINCT  
    ISNULL( a.[parentsupplierid],b.[supplierid]) [parentsupplierid],
    --A.[parentsupplierid]
    A.[supplierid],
    A.[suppliername],
    B.[suppliername] AS [PARENTsuppliername]
FROM   
    [whatsup].[dbo].[parent] A 
LEFT JOIN 
    (SELECT  
         [parentsupplierid],
         [supplierid], 
         [suppliername]
     FROM 
         [whatsup].[dbo].[parent]
     WHERE 
         [parentsupplierid] IS NULL) B ON A.parentsupplierid = B.supplierid

This query is not returning the expected result.

Please tell me how to write query to achieve this task in SQL Server.

Not understanding why 456 would be the parent of 546. How are they related? Same with 789 and 879... — sgeddes
– sgeddes, Commented Jun 22, 2018 at 15:40
It would make sense that anything without a parentsupplierID would be a parent, but your expected output doesn't follow that logic. It looks like 123, 546, 456, 879, and 789 should be parents? — dfundako
– dfundako, Commented Jun 22, 2018 at 15:41
there is no order or primary key to determine the logic here which is that you fill in the NULL with everything below it where the parentid isn't also a supplierid. — S3S
– S3S, Commented Jun 22, 2018 at 15:46

Laughing Vergil · Accepted Answer · 2018-06-22 16:26:19Z

There is a piece of logic here: "If [Current Record].[ParentSupplierId] Is Null AND [Previous Record].[ParentSupplierId] Is Null THEN [Current Record].[ParentSupplierId] = [Previous Record].[SupplierId]".

However, SQL records are not returned in the sequence inserted, and order of rows returned is not specified unless you use an ORDER BY clause. The simplest way to make this work is to add an ID field to your table, and use that for [Previous Record], like this:

CREATE TABLE [dbo].[parent]
(
    [parentId] int IDENTITY(1,1),
    [parentsupplierid] [int] NULL,
    [supplierid] [int] NULL,
    [suppliername] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) 
VALUES (123, 321, N'AAA'), (123, 231, N'BBB'),
       (NULL, 123, N'ABC'), (456, 654, N'DDD'),
       (NULL, 546, N'EEE'), (NULL, 456, N'DEF'),
       (789, 987, N'GGG'), (NULL, 879, N'HHH'),
       (NULL, 789, N'GHI')

Then to find the correct parent, using your base logic, try:

SELECT DISTINCT  
    ISNULL( a.[parentsupplierid],b.[supplierid]) [parentsupplierid],
    --A.[parentsupplierid]
    A.[supplierid],
    A.[suppliername],
    B.[suppliername] AS [PARENTsuppliername]
FROM   
    [whatsup].[dbo].[parent] A  --- Table for base records
LEFT JOIN [whatsup].[dbo].[parent] M  -- Join for missing parent
    ON M.[parentId] = (A.[parentId] - 1)
    AND M.[parentsupplierid] IS NULL
    AND A.[parentsupplierid] IS NULL
INNER JOIN [whatsup].[dbo].[parent] B
    ON ISNULL(A.parentsupplierid, M.supplierId) = B.supplierid

Collectives™ on Stack Overflow

How to get expected output in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related