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 .