Re: Subselect in SELECT

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 20 Jan 2007 18:55:40 GMT
Message-ID: <Xns98BE6F2B0C4BAanacedenthotmailcom@69.28.173.184>

Matthias Matker <kernkoenig_at_arcor.de> wrote in news:45b24ffe$0$5727$9b4e6d93_at_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

  SELECT persons.name, (SELECT COUNT(*) FROM person_cds

                        WHERE ( person_cds.pname= persons.name)) AMOUNT
  FROM
       persons

  WHERE ( amount< 280)
  ORDER by persons.name; Received on Sat Jan 20 2007 - 12:55:40 CST