Help building query to pull data from 3 tables

[madk]

Hello all,

 

I've need to come up with a query to display a count of all games grouped by platform in a users collection. I need to output these in alpha order of the platform name. My knowledge of sql is limited to basic queries and I feel a bit out of my league here. Here is the basic structure of my tables.

 

users

-------

id

 

collections

-------------

id

user_id

game_id

 

games

--------

id

platform

 

platforms

------------

id

name

 

Thanks for any help in advance!

[requinix]

Did you use the term "grouped by" intentionally?

 

1. Make a query to SELECT every user and every game they have in their collection, making sure it's ORDERed BY the fields and directions you want.

2. Make it return only three fields: the user id, platform id, and platform name. The platform id might be optional, depends.

3. Add a GROUP BY on the user id first, platform id or name (your choice - I'd pick the id) second.

4. Use the COUNT() function.

5. ???

6. Profit!

[madk]

Here is the query I was using but this was because I had the platform stored in the collections table where it doesn't belong:

 

SELECT collections.platform, COUNT(collections.id) AS count FROM collections, platforms WHERE collections.user_id = $user_id AND

collections.platform = platforms.id GROUP BY collections.platform ORDER by platforms.name ASC

[requinix]

So assuming you've moved the platform stuff where it belongs, what's your query now? At a minimum it'll have three tables, two JOINs, and a GROUP BY and ORDER BY.

[madk]

So assuming you've moved the platform stuff where it belongs, what's your query now? At a minimum it'll have three tables, two JOINs, and a GROUP BY and ORDER BY.

 

I don't have a query now, which is why I'm posting here for help. I can't wrap my brain around how the joins should be created.