How to find rows with duplicate columns in a subquery?

Question

I am working with the data in the EVE static dump and MS SQL Server Express.

The dump contains a table mapDenormalize, having columns named solarSystemID and typeID, both of which are ints (and neither of which is a key). I'm trying to find values of solarSystemID that appear more than once, with various combinations of typeID.

I have a query like

-- all systems that have a plasma or temperate planet
select distinct D.solarSystemID, D.typeID from mapDenormalize D 
  join invTypes T on D.typeID = T.typeID 
    where T.typeName in ('Planet (Plasma)', 'Planet (Temperate)') 
    order by solarSystemID

which returns 1 row for each solarSystemID that has a Plasma planet and 1 row for each with a Temperate planet. I am trying to figure out how to use this as a subquery to find solarSystemIDs that have both kinds of planets, but have come up empty-handed.

I started out thinking that I would do something like

select solarSystemID from ( the above query ) where count(solarSystemID) > 1

but that doesn't parse. What is the correct approach?

possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Michael, Oct 07 '13 at 17:54
@Michael is correct, the answer at his link contains the key: naming the subquery with "AS" so the columns in the subquery can be named. — wades, Oct 07 '13 at 18:04

MrSimpleMind · Answer 1 · 2013-10-07T18:04:47.980

0

select D.solarSystemID, count(D.solarSystemID) as counts
from mapDenormalize D 
     join invTypes T 
     on D.typeID = T.typeID 
where T.typeName in ('Planet (Plasma)', 'Planet (Temperate)')
group by D.solarSystemID
having count(D.solarSystemID) > 1;

The sqlfiddle

edited Oct 07 '13 at 18:04

answered Oct 07 '13 at 17:52

MrSimpleMind

7,890
3
40
45

score -1 · Answer 2 · answered Oct 07 '13 at 18:08

Name the subquery with AS, so the columns in it can be used as arguments to COUNT and GROUP BY

-- all systems that have at least 1 each plasma and temperate planet
select distinct Foo.solarSystemID from 
    (select D.solarSystemID, D.typeID from mapDenormalize D join 
    invTypes T on D.typeID = T.typeID 
        where T.typeName in ('Planet (Plasma)', 'Planet (Temperate)')) 
    as Foo group by Foo.solarSystemID having COUNT(Foo.solarSystemID) > 1

thanks to @Michael for pointing out the relevant question.

How to find rows with duplicate columns in a subquery?

2 Answers2