Re: Subselect in SELECT

From: Matthias Matker <kernkoenig_at_arcor.de>
Date: Sat, 20 Jan 2007 18:23:11 +0100
Message-ID: <45b24ffe$0$5727$9b4e6d93@newsspool3.arcor-online.net>

John K. Hinsdale schrieb:

> Matthias Matker wrote:

>> Hello together,
>>
>> SELECT
>> persons.name
>> ( SELECT COUNT(*) amount FROM person_cds WHERE ( person_cds.pname= persons.name))
>> FROM
>> persons
>> WHERE ( amount< 280)
>> ORDER by persons.name;
>> EXIT;
>>
>> I want the name of the persons ( name is primary key of persons), which have fewer cds than 280.
>>

> 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"?

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"

in

  SELECT

       persons.name
       ( SELECT COUNT(*) amount FROM person_cds WHERE ( person_cds.pname= persons.name))
  FROM
       persons

  WHERE ( amount< 280)
  ORDER by persons.name;

and why occurs this error and have to make my solution running well?

Matze Received on Sat Jan 20 2007 - 11:23:11 CST