football squares sql
Author
I'm making a football squares game for the web, and I need some help crafting a SQL query for it.
Basically, there's a table called standardgame, which lists all the different contests available (i.e. one for every football game). Each entry has a gameid.
Then, there's a table called purchasedsquares, which has a row, column, username, and gameid.
How do I create a query that selects all the games AND how many squares have been purchased for each game? So, this would involve both the
standardgame table and the purchasedsquares table.
Mike C.
http://www.coolgroups.com/zoomer/
So something like:
If the value of "how many squares have been purchased for each game" is determined by some column, not just the number of matching records in purchasedsquares (for example if purchasedsquares has a column called "purchases"), then replace COUNT() with SUM(purchasedsquares.purchases)
Also, if you only want games with 1 or more purchases, use an INNER JOIN, and if you need to include zero purchase games but also want to filter purchasedsquares, you'll need to use a subquery or provide an OR IS NULL, otherwise all the zero's will be discarded because they don't match the criteria
SELECT standardgame.gameid,COUNT(purchasedsquares.gameid) AS PurchaseCountFROM standardgameLEFT OUTER JOINpurchasedsquares ON standardgame.gameid = purchasedsquares.gameidGROUP BY standardgame.gameidResults:gameid PurchaseCount1 52 03 2If the value of "how many squares have been purchased for each game" is determined by some column, not just the number of matching records in purchasedsquares (for example if purchasedsquares has a column called "purchases"), then replace COUNT() with SUM(purchasedsquares.purchases)
Also, if you only want games with 1 or more purchases, use an INNER JOIN, and if you need to include zero purchase games but also want to filter purchasedsquares, you'll need to use a subquery or provide an OR IS NULL, otherwise all the zero's will be discarded because they don't match the criteria
Author
Looks like this is what I was going for. I'm impressed. Thanks a lot.
Mike C.
http://www.coolgroups.com/zoomer/
Mike C.
http://www.coolgroups.com/zoomer/
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
