Re: Subselect in SELECT

From: Matthias Matker <kernkoenig_at_arcor.de>
Date: Sat, 20 Jan 2007 20:57:46 +0100
Message-ID: <45b27439$0$18837$9b4e6d93@newsspool4.arcor-online.net>

Ana C. Dent schrieb:

> 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;

Sorry, this doesn't work, the same error occurs.

Matze Received on Sat Jan 20 2007 - 13:57:46 CST