Sign in to follow this  

football squares sql

This topic is 4584 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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/

Share this post


Link to post
Share on other sites
So something like:


SELECT
standardgame.gameid,
COUNT(purchasedsquares.gameid) AS PurchaseCount
FROM standardgame
LEFT OUTER JOIN
purchasedsquares ON standardgame.gameid = purchasedsquares.gameid
GROUP BY standardgame.gameid


Results:

gameid PurchaseCount
1 5
2 0
3 2


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

Share this post


Link to post
Share on other sites
Looks like this is what I was going for. I'm impressed. Thanks a lot.

Mike C.
http://www.coolgroups.com/zoomer/

Share this post


Link to post
Share on other sites
Sign in to follow this