Count across 3 tables

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!

Re: Count across 3 tables

On 23 Sep, 23:49, JSOUL Rocks
wrote:
> 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!

Do not multi-post. Cross-post if you must but don't multi post It
wastes
people's time. http://www.blakjak.demon.co.uk/mul_crss.htm.

Re: Count across 3 tables

select country.countryname,consolebrands.brandname,
console.model_number,count(*) as number_of_owners

from country, members, consolebrand,console

where
members.contrynum = country.contrynum
and members.consoleid = console.consoleid
and console.consolebrandid = consolebrand.brandid

group by country.countryname,consolebrands.brandname, console.model_number







JSOUL Rocks wrote:
> 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!
>
>