« SQL inner join and counting query?
Can the Dems Turn the US into Iraq by having the Superdelegates Select Hillary Over Obama? »

SQL: Cant link sub-queries?

Im supposed to list the names of actors who have stared in more than 3 films

This is wrong but its all i can think of and everything needed should be there.

SELECT fname, surname
FROM actor
WHERE a_id=(SELECT actor_id FROM acts WHERE movie_id=(SELECT Count(*) FROM acts GROUP BY actor_id HAVING (COUNT(*)>3)));

Its wrong in the first subquery but i dont know what to do?
SELECT fname, surname
FROM actor
WHERE a_id=(SELECT actor_id FROM acts GROUP BY actor_id HAVING (COUNT(*)>3));

This is better but wont work because the sub querie returns 2 answers and it only allows 1 for sub queries?

SELECT fname, surname
FROM actor
WHERE a_id IN (SELECT actor_id FROM acts GROUP BY actor_id HAVING (COUNT(*)>3));

This entry was posted on Sunday, November 29th, 2009 at 3:51 am and is filed under subquery. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “SQL: Cant link sub-queries?”

  1. I really dont know your tables structure. This is what I think will work for you.

    select fname,surname from actor where a_id in(select actor_id from (select actor_id,count(*) b from acts group by actor_id) where b>3)
    References :

  2. SELECT fname, surname
    FROM actor
    WHERE a_id IN (SELECT actor_id FROM acts GROUP BY actor_id HAVING (COUNT(*)>3));
    References :
    http://www.sql-ex.ru/help/

  3. Try changing your "a_id =" to "a_id IN" – that should solve the problem.
    References :

Leave a Reply