I have the following sql query that joins two select statements on two columns
SELECT * FROM (SELECT TOP(100) Name ,ItemNum ,TicketNum FROM [dbo].[dd] ) t1 JOIN (SELECT TOP (100) TicketNum ,ItemNum FROM [dbo].[dd]) t2 ON t1.ItemNum = t2.ItemNum AND t1.TicketNum = t2.TicketNum
The query produces the following output.
| Name | ItemNum |TicketNum |TicketNum |ItemNum |
But I would like the output to be | Name | ItemNum |TicketNum |
Advertisement
Answer
Solution 1: Specify the column names like this
SELECT t1.Name, t1.ItemNum, t1.TicketNum FROM ( SELECT TOP 100 Name, ItemNum, TicketNum FROM [dbo].[dd] ) t1 INNER JOIN ( SELECT TOP 100 TicketNum, ItemNum FROM [dbo].[dd] ) t2 ON t1.ItemNum = t2.ItemNum AND t1.TicketNum = t2.TicketNum
Solution 2:
SELECT t1.* FROM ( SELECT TOP 100 Name, ItemNum, TicketNum FROM [dbo].[dd] ) t1 INNER JOIN ( SELECT TOP 100 TicketNum, ItemNum FROM [dbo].[dd] ) t2 ON t1.ItemNum = t2.ItemNum AND t1.TicketNum = t2.TicketNum