CODEWITHSUNDEEP
sqloracle-databasecorrelated-subquery
tomekn
tomekn

Reputation: 31

Correlated subqueries with inline view as outer query

Is it possible to correlate subquery with an inline view?

Suppose we have simple database that stores information about pet owners. 

SELECT p.name,  
       p.surname, 
       p.num_of_pets 
  FROM (SELECT person.id_person id_person, 
               person.name      name, 
               person.surname   surname, 
               COUNT(DISTINCT person_pets.id_pet) num_of_pets
          FROM person 
               LEFT JOIN person_pets 
                      ON person.id_person=person_pets.id_person) p 
  WHERE EXISTS(SELECT 1 
                 FROM person p1
                WHERE p1.father=p.id_person 
                   OR p1.mother=p.id_person
                  AND p.num_of_pets > 2)

I know it's quite strange example, but only wanted to show what I mean. When I run such query I get table or view does not exist in the place of p table occurrence in subquery. I suppose I misunderstand how inline views are processed. Are there any limitations in usage compared to views created with 'with' or 'create view' or to existing tables?

Upvotes: 0

Views: 768

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I suspect that you intend this:

SELECT p.name, p.surname, p.num_of_pets 
FROM (SELECT p.id_person, p.name, p.surname, 
             COUNT(pp.id_person) as num_of_pets
      FROM person p LEFT JOIN
           person_pets pp
           ON p.id_person = pp.id_person
      GROUP BY p.id_person, p.name
     ) p 
WHERE EXISTS (SELECT 1 
              FROM person p1
              WHERE (p1.father = p.id_person OR p1.mother = p.id_person) AND
                    p.num_of_pets > 2
             );

I'm not sure why you are getting that particular error, but some notes:

  • The subquery needs a GROUP BY to be syntactically correct. The COUNT() without a GROUP BY should result in an error.
  • You shouldn't need COUNT(DISTINCT). It has additional overhead, so it is best to get around it.
  • The logic in the outer query probably needs parentheses (unless you want fathers with no pets).
  • I have a strong preference for using table aliases.

Upvotes: 1

Related Questions