[SOLVED] User Ranking

[DEVILofDARKNESS]

Okay so I want to make a ranking page for my game, in which people can see how they score against other players...

 

I have the following tables:

nations: nation_id, nation_name

regions: region_id, region_name, nation_id

 

The id's are primary keys, and auto_increment,

So I want that the nation with the most  regions is given:

 

example:

Nation_Name  Regions

DEVIL                5

DARKNESS        2

 

But I really have NO idea how I should begin on this one,

so please can someone give me a hint.

[DEVILofDARKNESS]

I currently have:

SELECT nation_name, (SELECT count( region_id ) FROM regions) AS regioncount FROM regions ORDER BY regioncount DESC

 

but I'm stuck with declaring SELECT count(region_id) FROM regions WHERE nation_id = ...

I don't know how to make this work.

[gevans]

I believe something along the lines of the following is the query you want;

 

SELECT `nation_name`, COUNT(`region_id`) AS `regions_count` 
FROM `nations` AS `n`, `regions` AS `r`
GROUP BY `r`.`nation_id`
ORDER BY `regions_count`

[DEVILofDARKNESS]

Well this gives me:

nation_name region_count

Belgium        6

Belgium        6

Belgium        6

Belgium        6

Belgium        18

Belgium        126

 

There are more nations then Belgium and There are only 28 regions

 

 

BUT this doesn't matter,

because thanks to you code I saw an other way to solv'it (you couldn't know becuase you hadn't enough info)

the next one:

SELECT `nation_id` , COUNT( `region_id` ) AS `regions_count` 
FROM `regions` 
GROUP BY `regions`.`nation_id` 
ORDER BY `regions_count` DESC

 

And now I'm able todo a second query which is something like:

SELECT 'nation_name' FROM nations WHERE nation_id='$nationid';

 

Thanks a lot!

[fenway]

I don't understand what you're tryign to do with the second part.

[DEVILofDARKNESS]

I already had deleted that second part...

but I thought I didn't had to post it here ...