Including count(x) = 0

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Including count(x) = 0

Post 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.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

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

?>
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post 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?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
Post Reply