DEVILofDARKNESS Posted September 9, 2009 Share Posted September 9, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/ Share on other sites More sharing options...
kickstart Posted September 9, 2009 Share Posted September 9, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/#findComment-915536 Share on other sites More sharing options...
DEVILofDARKNESS Posted September 9, 2009 Author Share Posted September 9, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/#findComment-915538 Share on other sites More sharing options...
kickstart Posted September 9, 2009 Share Posted September 9, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/#findComment-915543 Share on other sites More sharing options...
DEVILofDARKNESS Posted September 9, 2009 Author Share Posted September 9, 2009 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 ... Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/#findComment-915544 Share on other sites More sharing options...
kickstart Posted September 9, 2009 Share Posted September 9, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/173678-solved-on-localhost-sql-works-on-server-not/#findComment-915580 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.