Re: SQL help

The following query should work. It counts up the number of children per agent, and displays those agents that have one or more children.

SELECT a.agent_name, COUNT(ac.id_child)
FROM agent a, agent_child ac
WHERE a.id_agent = ac.id_agent
GROUP BY a.agent_name
HAVING COUNT(ac.id_child) >= 1;

I think that in this specific case that you could do away with the HAVING clause. An inner join should never return rows without children, so COUNT(ac.id_child) should never be 0 for any given agent. I don't know if that optimization would buy you anything though, and risk is probably greater of confusing some poor programmer down the road, so I'd leave the HAVING in.

Jonathan

On Mon, 13 Mar 2000 09:03:34 -0800, ummo14 <ummo14NOumSPAM_at_hotmail.com.invalid> wrote:

>
>I have 3 tables
>table1:Agent
>
>column: id agent
>column: agent name
>
>table2:Agent_child
>column: id agent
>column: id child
>
>table3:Child
>column: id child
>column: name child
>
>If a Sql guru could help me It will be great for me. I want
>to select the name of the agents which have at least one
>child (if it's possible in displaying the number of child
>beside the name of the agents)
>
>
>
>
>* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
Received on Tue Mar 14 2000 - 00:00:00 CST