CODEWITHSUNDEEP
mysqlsqlsubquery
Chris
Chris

Reputation: 1058

Use column in subquery, but not in the main query's select

First these are my tables:

 Foo (id, Name)
 Bar (foo_id, num)

I am trying to do the following: I want to select the Foos where all its Bars have a num higher than 10. The query below works perfectly fine and does that, but I don't want to have the Foo.id in the select. When I remove the Foo.id from the select, I get an error that the column is not found in the where clause (Of the subquery).

#1054 - Unknown column 'Foo.id' in 'where clause'

How can I remove the Foo.id from the select, but still use the column in the subquery's where clause? I tried joining the Foo table in the subquery and use Name in the where, but this does not give me the desired results as Name is not unique.

SELECT 
    Foo.Name, 
    Foo.id
FROM
    Foo
    LEFT  JOIN Bar ON Bar.foo_id = Foo.id
GROUP BY Bar.foo_id
HAVING COUNT(Bar.foo_id) = ( 
    SELECT 
        COUNT(Bar.foo_id) 
    FROM 
        Bar 
    WHERE 
        num > 10 
    AND 
        Bar.foo_id = Foo.id GROUP BY Bar.foo_id 
    )

Thank you in advance!

Upvotes: 2

Views: 1158

Answers (1)

John Woo
John Woo

Reputation: 263703

here's another simple way,

SELECT  a.Name
FROM    Foo a
        INNER JOIN Bar b
            ON a.ID = b.foo_ID
GROUP   BY a.ID, a.Name
HAVING  COUNT(b.foo_ID) = SUM(b.num > 10)

Upvotes: 3

Related Questions