CODEWITHSUNDEEP
sql-serversql-server-2008
rame bk
rame bk

Reputation: 99

How to get expected output in SQL Server

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.

Upvotes: 1

Views: 169

Answers (1)

Laughing Vergil
Laughing Vergil

Reputation: 3756

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

Upvotes: 1

Related Questions