Re: Subselect in SELECT

From: John K. Hinsdale <hin_at_alma.com>
Date: 20 Jan 2007 16:31:28 -0800
Message-ID: <1169339488.819612.308810@a75g2000cwd.googlegroups.com>

Matthias Matker wrote:
> John K. Hinsdale schrieb:

> > The preferred way in SQL to express your desired result is something
> > like:
> >
> > SELECT P.name, count(*) AS no_of_cds
> > FROM persons P, person_cds PC
> > WHERE P.name = PC.pname
> > GROUP BY P.name
> > HAVING count(*) < 280
> > ORDER BY P.name
> >
>
> Thanks, but what is wrong in my "solution"?

Hi Matthias,

Nothing "wrong" with it; its just a lot harder to write and understand than it needs to be. That is, unless you're designing, grading, or doing a homework assignment, with the purpose of exercising the subquery functionality of SQL.

> My only problem is to get the value of
> ( SELECT COUNT(*) amount FROM person_cds WHERE
> ( person_cds.pname= persons.name))
> to be stored as "amount"

OK, this should work if you insist on using subqueries:

    SELECT name, amount
    FROM ( SELECT persons.name,

                ( SELECT count(*)
                  FROM person_cds
                  WHERE person_cds.name = persons.name
                ) AS amount
           FROM persons
         )

    WHERE amount > 2
    ORDER BY name

But now you've got three SELECTs where a simple join will do! Please tell me this is some sort of exercise and not real work. ;)

HTH,
        John Hinsdale Received on Sat Jan 20 2007 - 18:31:28 CST