[SOLVED] on localhost sql works on server not?

[DEVILofDARKNESS]

I have the following query which perfectly works on my localhost, but doesn't run on my webserver (hosted at combell.com) although it is exact the same database

 

SELECT nations.nation_name, nations.nation_id, (SELECT SUM( weapons.power) * region_weapons.ammount) As power FROM weapons INNER JOIN region_weapons ON region_weapons.weapon_id = weapons.weapon_id INNER JOIN regions ON regions.region_id = region_weapons.region_id INNER JOIN nations ON nations.nation_id = regions.nation_id GROUP BY nations.nation_name ORDER BY power DESC

 

Why is this possible and what can I do?

[kickstart]

Hi

 

Bit unsure what you are trying to do with the subselect. Assuming multiple region weapons per region and multple regions per nation the region_weapons.ammount would appear to be pretty random.

 

What error do you get?

 

All the best

 

Keith

[DEVILofDARKNESS]

Indeed their are more regions per nation

and each region can have more weapons

 

I actually don't get an error, I get a null value for the power, but I'm sure it can't be a null value I know it should be an integer

[kickstart]

Hi

 

My brain hurts and I am certain I have done this inefficiently but at this moment can't think of a better way.

 

However, this would seem logically better:-

 

SELECT nations.nation_name, nations.nation_id, SUM(tmpPower) AS power 
FROM nations 
INNER JOIN regions ON nations.nation_id = regions.nation_id 
INNER JOIN (SELECT region_weapons.region_id , weapons.power * region_weapons.ammount AS tmpPower
FROM weapons 
INNER JOIN region_weapons 
ON region_weapons.weapon_id = weapons.weapon_id ) Deriv1 ON regions.region_id = Deriv1.region_id
GROUP BY nations.nation_name 
ORDER BY power DESC[code]

All the best

Keith

[DEVILofDARKNESS]

Looks like you forgot a / ;p

 

 

BTW: You Solved it!

A bonus point for Keith

 

PS: Man your brain is a genius! How where you able to find the complicated INNER JOIN (SELECT ...

[kickstart]

Hi

 

That was the easy bit. Just do it in stages and join your stages together.

 

Difficult bit is I am sure there is a far more efficient way to do it but I can't think of it.

 

All the best

 

Keith