Re: Subselect in SELECT

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

Matthias,
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

The above uses a few concepts you should probably explore:

• joining two tables together
• using GROUP BY to summarize data to a higher level of detail than what is in your rows
• using an aggregate function like COUNT

Suggest you get a book on SQL and learn the above concepts. Hope that is helpful.
John Hinsdale Received on Sat Jan 20 2007 - 10:23:45 CST