|
|
Posted by Sιverin Richard on 10/02/07 10:13
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!
>
>
Navigation:
[Reply to this message]
|