October 26th, 2004, 03:58 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
it is considered bad etiquette to (1) ask a new question by posting onto an existing unrelated thread, and (2) ask people to leave devshed to visit some other site
your problem is that your two relationships are totally unrelated to each other, consequently any grouping query is going to have cross join effects
your solution is:
Code:
select EMP.id as [ID]
, EMP.empname as [Name]
, count(EMP_appreciation.sal) as [No. Bonus]
, max(EMP_appreciation.sal) as [Max Bonus]
, ( select count(eid)
from EMP_projects
where eid = EMP.id ) as [Projects Count]
from EMP
left outer
join EMP_appreciation
on EMP.id
= EMP_appreciation.eid
group
by EMP.id
, EMP.empname
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
