Remove duplicate columns after using join on in sql-CodePudding

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.

But I would like the output to be | Name | ItemNum |TicketNum |

CodePudding user response：

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