|
|
Posted by JSOUL Rocks on 09/23/07 22:49
I've got a few different tables that track video game consoles people
use in their profile. The tables are below. I'm trying to do two
counts/sorts and display them properly.
(1) sort the fields by country
(2) In each country, display the number consoles owned by members
sorted by brand and model. So it should output like this:
country, brand name, model number, number of owners
US, microsoft, xbox1, 5432
US, sony, playstation1, 4345
US, sony, playstation2, 5456
Mexico, microsoft, xbox1, 4432
Mexico, sony, playstation2, 4545
etc.
The tables I have are as follows and the problem is that I could
reference 2 tables but not 3. The challenge is that the reference
between:
(1) members and countries tables (countrynum and countryname)
(2) members and consoles tables (consolemodelid = consoleid)
(3) consoles and consolebrands tables (consolebrandid = brandsid)
and then each ID has to do a lookup to return the name associated with
the ID, the console model to consoleid, the brandsname to the
brandsid, and the countryname to the countryid.
members table
userid
username
countrynum
consolemodelid
consoles table
consoleid
consolemodel
consolebrandid
countries table
countryid
countryname
consolebrands table
brandsid
brandsname
Right now I've been working with this query but I'm can't handle all
these tables since there are now three cross referenced instead of
just two:
SELECT consolebrands.brandsname, members.consolemodelid, count( * )
AS count
FROM members, consoles, consolebrands, countries where (
(members.consolemodelid = consoles.consoleid) and
(consoles.consolebrandid = consolebrands.id) )
GROUP BY consolebrands.brandsname
ORDER BY count DESC
And I'm totally stuck! As it has been a while, I'm hoping someone can
help me out as I learn more complex queries. Thank you!
Navigation:
[Reply to this message]
|