query returns nothing. The count has to be at least 1 or more. ANy
ideas?
Select fname+' '+lname as full_name,SubQuery.Wins, Query.losses from
tbl_Participants a inner
join
(Select Part_ID,count(*) as Wins from tbl_Weekly_Picks where pick_winner
= actual_winner and actual_winner <> 0 group by part_id)
SubQuery
ON
a.Part_ID=SubQUery.Part_ID
inner
join
(Select Part_ID,count(*) as losses from tbl_Weekly_Picks where
pick_winner
<> actual_winner and actual_winner <> 0 group by part_id)
Query
ON
a.Part_ID=Query.Part_ID
order by wins desc
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I think you might want to look at Outer joins rather than Inner
________________________________________
Atrax. MVP, IIS
http://rtfm.atrax.co.uk/
newsflash : Atrax.Richedit 1.0 now released.
http://rtfm.atrax.co.uk/infinitemon...Atrax.RichEdit/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SELECT MIN(fname+' '+lname) AS full_name,
COUNT(CASE WHEN pick_winner = actual_winner THEN 1 END) AS wins,
COUNT(CASE WHEN pick_winner <> actual_winner THEN 1 END) AS losses
FROM tbl_Weekly_Picks AS W
JOIN tbl_Participants AS P
ON W.part_id = P.part_id
GROUP BY part_id
(untested)
--
David Portas
----
Please reply only to the newsgroup
--
