Sign in to follow this  
mike74

football squares sql

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this