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