Re: query sql problem

From: <suh_anny_at_my-deja.com>
Date: 2000/03/13
Message-ID: <8aj9fk$42d$1@nnrp1.deja.com>#1/1

Let's tables be:

create table Agent
( ID_A NUMBER,

   NAME_A VARCHAR2(10)) create table Agent_Child
( ID_A NUMBER,

   ID_CH NUMBER ) create table Child
( ID_CH NUMBER,

   CH_NAME VARCHAR2(10)) To select Agent's names, who have at least :N (variable) childs you have to

SELECT t1.name_a, COUNT(t2.id_ch)
FROM Agent t1, Agent_Child t2
WHERE
  t1.id_a=t2.id_a
  GROUP BY t1.name_a
HAVING COUNT(t2.id_ch)>:N

//-----------------------------------------

you have to use HAVING because, you have GROUP keyword. Because Oracle varify WHERE clause first and only then make GROUP, that is why you cannot use WHERE COUNT(t2.id_ch)>:N

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 13 2000 - 00:00:00 CST