CODEWITHSUNDEEP
sqlsql-server
user3904868
user3904868

Reputation:

Using query result as subquery syntax

I have a table that I need to identify duplicate entries to delete them. I can find the duplicates using the following query

select s.*, t.* 
from [tableXYZ] s
join (
    select [date], [product], count(*) as qty
    from [tableXYZ]
    group by [date], [product]
    having count(*) > 1
) t on s.[date] = t.[date] and s.[product] = t.[product]
ORDER BY s.[date], s.[product], s.[id]

and then need to use the result from this table to show where [fieldZ] IS NULL

I've tried the following but get error The column 'date' was specified multiple times for 'subquery'.

select * from
(
    select s.*, t.* 
    from [tableXYZ] s
    join (
        select [date], [product], count(*) as qty
        from [tableXYZ]
        group by [date], [product]
        having count(*) > 1
    ) t on s.[date] = t.[date] and s.[product] = t.[product]
) as subquery
where [fieldZ] is null

Upvotes: 0

Views: 32

Answers (1)

user6691848
user6691848

Reputation:

You have column date in your subquery twice because you are selecting s.* and t.*, this will return s.Date and t.date. If you need both columns, alias one of the columns.

You will also run into this problem with the product column. Your subquery cannot return multiple columns with the same name. Only select the columns you need in your subquery instead of selecting all columns. This is a good practice in general and will solve this issue.

Upvotes: 1

Related Questions