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
me out as I learn more complex queries. Thank you!

On 23 Sep, 22:07, JSOUL Rocks
<tkd_NO_SPAM_inthecity@yah_NO_SPAM_oo.com> 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
> 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.