Re: query sql problem

A bit more info/help for you :

create

table	agent
	(
	a_id	number(6)


	,a_name	varchar2(30)

	);
	
create
table	agent_child
	(
	a_id	number(6)


	,c_id	number(6)

	);
	
create
table	child
	(
	c_id	number(6)


	,c_name	varchar2(30)

	);
	
insert
into	agent
	(
	a_id


	,a_name

	)
values	(
	1


	,'AGENT 1'

	);

insert
into	agent
	(
	a_id


	,a_name

	)
values	(
	2


	,'AGENT 2'

	);

insert
into	agent
	(
	a_id


	,a_name

	)
values	(
	3


	,'AGENT 3'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	1


	,'CHILD 1'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	2


	,'CHILD 2'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	3


	,'CHILD 3'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	4


	,'CHILD 4'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	5


	,'CHILD 5'

	);

insert
into	child
	(
	c_id


	,c_name

	)
values	(
	6


	,'CHILD 6'

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	1


	,1

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	1


	,2

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	2


	,3

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	2


	,4

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	2


	,5

	);
	
insert
into	agent_child
	(
	a_id


	,c_id

	)
values	(
	2


	,6

	);
	
	
select  a.a_name
        ,c.c_name
from    child c
        ,agent_child ac
        ,agent a
where   a.a_id = ac.a_id
and     c.c_id = ac.c_id;

A_NAME	C_NAME
AGENT 1	CHILD 1
AGENT 1	CHILD 2
AGENT 2	CHILD 3
AGENT 2	CHILD 4
AGENT 2	CHILD 5
AGENT 2	CHILD 6

select	a.a_name


	,count(*)

from	agent_child ac


	,agent a

where	a.a_id = ac.a_id
group
by	a.a_name;

A_NAME	COUNT(*)
AGENT 1	2
AGENT 2	4

Hope this helps.

Roger.

ummo14 wrote:

> 
> i have 3 tables:
> 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