Using query result as subquery syntax

Question

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

user6691848 · Accepted Answer · 2017-04-10 17:55:35Z

1

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.

answered Apr 10, 2017 at 17:55

user6691848

Sign up to request clarification or add additional context in comments.

1 Comment

pmbAustin Over a year ago

Just to add emphasis... we have a standard that no SQL should ever use "*" for production code. We use it purely as a debugging aid to quickly prototype queries and test things only. ALWAYS list all the columns you're actually interested in, and ONLY those you're actually interested in for any given query.

Collectives™ on Stack Overflow

Using query result as subquery syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related