get required output in sql server

Question

get required output in sql server

srinudw88

SSC Veteran

Points: 298
More actions
June 22, 2018 at 9:50 am

#355634

Hi ,I have one doubt in 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')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (123, 231, N'BBB')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 123, N'ABC')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (456, 654, N'DDD')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 546, N'EEE')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 456, N'DEF')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (789, 987, N'GGG')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 879, N'HHH')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (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 below:
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
above query is not given expected result.
please tell me how to write query to achive this task in sql server .

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Since the sample data in the assignment question doesn't have a Parent Supplier ID for Supplier IDs 546 and 879 it will be impossible to produce the desired output. You need clarification on what is being asked of you from your teacher.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

srinudw88 - Friday, June 22, 2018 9:50 AM
Hi ,I have one doubt in 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')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (123, 231, N'BBB')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 123, N'ABC')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (456, 654, N'DDD')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 546, N'EEE')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 456, N'DEF')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (789, 987, N'GGG')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (NULL, 879, N'HHH')
GO
INSERT [dbo].[parent] ([parentsupplierid], [supplierid], [suppliername]) VALUES (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 below:
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
above query is not given expected result.
please tell me how to write query to achive this task in sql server .

There's no way to do it. There's nothing defining that 879 has 789 as a parent. Same with the other missing match. You can't rely on the order because there's no order inside a table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2