OraFAQ Forum: SQL & PL/SQL » count function with outer Join on Oracle8i

Home » SQL & PL/SQL » SQL & PL/SQL » count function with outer Join on Oracle8i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

count function with outer Join on Oracle8i [message #19549]

Tue, 26 March 2002 23:40

Jean-Luc Blanc
Messages: 1
Registered: March 2002

Junior Member

I have a problem with outer join:
To simplify I have tree tables:

TEAM
-------
ID_TEAM
NAME

TECHNINCIAN
---------------
ID_TECH
ID_TEAM
NAME
FIRSTNAME

APPOINTMENT
-----------
ID_APOINT
FK_TECH
STATUS

A team contain technicians
A technician has appointments

I want for one team the list of all the technicians with the number of appointment which status is �reserved� (0 or n).

My request is not working. Why ?

SELECT e.NAME,e.FIRSTNAME,count(a.STATUS) as nbAppoint
FROM technician e,appointment a
WHERE e.ID_TECHNICIAN(+)=a.FK_TECH
and a.STATUS='reserved'
and e.FK_TEAM in(select distinct t.ID_TEAM from team t where t.NAME='JL_Team')
GROUP BY e.NAME,e.FIRSTNAME
ORDER BY 1

Thank's

Report message to a moderator