Remove duplicate columns after using join on in sql

Question

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 |

Does this answer your question? [Select only some columns from a table on a JOIN](https://stackoverflow.com/questions/1329662/select-only-some-columns-from-a-table-on-a-join) — philipxy, Mar 26 '22 at 01:12

Nayanish Damania · Accepted Answer · 2022-03-24T11:56:23.230

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

Remove duplicate columns after using join on in sql

1 Answers1