CODEWITHSUNDEEP
sqlcountsubquery
Christian
Christian

Reputation: 11

Issue with SQL query

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

Upvotes: 1

Views: 66

Answers (4)

Alex S
Alex S

Reputation: 1221

This query should work

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

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

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

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171381

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
)

Upvotes: 8

Related Questions