Page 1 of 1

Including count(x) = 0

Posted: Fri Jan 16, 2004 7:51 am
by Shendemiar
I'm doing a hall of fame list and I have two tables:

Players:

PID-NICK (Playerid,Nick)
1-KANA
2-KOIRA
3-KETTU

And

Games:
GID-WID (Gameid, Winnerid)
1-1
2-1
3-2

What query gives the following result:
NICK-NUMBER OF WINS
KANA-2
KOIRA-1
KETTU-0

I dont know how to get the KETTU included since he has no wins.

Posted: Fri Jan 16, 2004 8:50 am
by kettle_drum
Surely you dont need to show the members who have no points on the hall of fame as they have no..."fame". If you do then just reverse the query you have to get the ones with points, so you only get names of people with no points.

?>

Posted: Fri Jan 16, 2004 9:03 am
by Shendemiar
IT's a rather a player list than hall of fame, so i need to have all of them

Code: Select all

Select PID as Nick, Count(WID) as WINS from Players P games G where P.PID=G.WID

Results:
NICK-WINS
KANA-2 
KOIRA-1
How can i 'reverse' it?

Posted: Fri Jan 16, 2004 9:09 am
by kettle_drum
Well you could just then select the members with zero points. Or to too make things a lot easier you could store the points in the same table as the username and id, as afterall its data that is highly related.