Issue with SQL query

Question

This is supposed to be a very simple problem, but somehow I am completely stuck... This is a simplified example to show the problem:

Table:
int     ID (primary key)
varchar Child
varchar Toy

Running

SELECT Child, Toy FROM TestTable

gives me:

Child    Toy
------------------------
A        T_A
B        T_B
A        T_C
C        T_D  
A        T_E
B        T_F

I need a query that returns me the Child/Toy entries only for children that have more then 2 toys - only one in this example. So the output should look like this:

Child    Toy
------------------------
A        T_A
A        T_C
A        T_E

For clarity, it would be useful if you could specify which flavour of SQL you are programming for — Andrew
– Andrew, Commented Jan 13, 2012 at 16:08

D'Arcy Rittich · Accepted Answer · 2012-01-13 16:07:31Z

8

This finds children with more than two toys:

select Child
from TestTable
group by Child
having count(*) > 2

Then you can do this to get all columns back that you want:

select Child, Toy
from TestTable 
where Child in (
    select Child
    from TestTable
    group by Child
    having count(*) > 2
)

answered Jan 13, 2012 at 16:07

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

3 Comments

JNK Over a year ago

Actually it finds children with more than one ROW. OP should be advised this will count duplicate toys as well.

MatBailie Over a year ago

+1 : And, I'd JOIN rather than using IN. But it's the same concept :)

Christian Over a year ago

Thank you for your really fast response, but this does not give me the desired output. I need 'Child' and 'Toy' in the output, so SELECT Child, Toy ....

mwigdahl · Accepted Answer · 2012-01-13 16:09:16Z

3

SELECT Child, Toy
FROM TestTable
WHERE Child IN
(
    SELECT Child
    FROM TestTable
    GROUP BY Child
    HAVING COUNT(*) > 2
)

answered Jan 13, 2012 at 16:09

mwigdahl

16.6k7 gold badges52 silver badges64 bronze badges

Comments

UnhandledExcepSean · Accepted Answer · 2012-01-13 16:09:54Z

1

SELECT Child, Toy
FROM TestTable
WHERE child IN(
   SELECT Child FROM TestTable group by Child having count(*)>2
)

answered Jan 13, 2012 at 16:09

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

Comments

Alex S · Accepted Answer · 2012-01-13 16:11:13Z

0

This query should work

SELECT
    Child,
    Toy
FROM TestTable t
WHERE
    t.Child IN 
    (
        SELECT Child
        FROM TestTable  
        GROUP BY Child
        HAVING COUNT(*) > 2         
    )

answered Jan 13, 2012 at 16:11

Alex S

1,2411 gold badge12 silver badges27 bronze badges

Collectives™ on Stack Overflow

Issue with SQL query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related