I have this SQL:

SELECT

U.ID,
U.PrefID,
U.LastName,
U.FirstName,
U.FirstLogin,
U.LastLogin,
UserActive =
CASE
WHEN (U.LastLoginAppVersion = '' OR U.LastLoginAppVersion IS
NULL) THEN 'No'
ELSE 'Yes'
END,
COUNT(DISTINCT A.ID) AS AssingedIssues,
COUNT(DISTINCT R.ID) AS ReportedIssues

FROM

dbo.t_UserNames U
LEFT OUTER JOIN dbo.t_Issues A ON U.ID = A.AssignedTo_ID
LEFT OUTER JOIN dbo.t_Issues R ON U.ID = R.ReportedBy_ID

GROUP BY

U.ID,
U.PrefID,
U.LastName,
U.FirstName,
U.FirstLogin,
U.LastLogin,
U.LastLoginAppVersion

My question is, if I don't use the Distinct funtion in the count I get
a cartasian effect. Is there a better way to achive my results?

Hi,

do the joins after your aggregation (perhaps with subqueries) This will
get you the information afterwards, without getting the cartesian
effects.

Something like

SELECT Name, Restofthegrouping
FROM
(
Here goes your grouping query
) GropingQuery
LEFT JOIN Whatever

If you just need distinct information from the LEFT joined tables, you
can first do some thing like a distinct on those tables.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---