hyster Posted October 5, 2015 Share Posted October 5, 2015 the count part works ok but it fails on the joins, the examples / tutorials I have read look like I have done, but I didn't find 1 with the count in front of it. the aim is for a table like this. level = 8 tank count <alt text on count> IS-3 3 player 1, player2, player 3 E-100 2 player1, player 4 thanks for any help Select Count(Distinct tank_id) As counttanks , Count(tank_id) As counttanks , tank_id As tank_id From garage_list Group By tank_id // upto here it works right JOIN tank_list on garage_list:tank_id = tank_list:tank_id right JOIN player_list on garage_list:account_id = player_list:account_id where tank_list.level='8', and player_list.clan'some-name' TABLES <garage_list> account_id :: tank_id <tank_list> Tank_id :: name :: long_name :: row1 :: row2 :: row3 :: <player_list> account_id :: clan :: nickname Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 5, 2015 Share Posted October 5, 2015 Your group_by is in the wrong place. Quote Link to comment Share on other sites More sharing options...
hyster Posted October 5, 2015 Author Share Posted October 5, 2015 I put the group by there as the garage_list:account_id is not a unique value. I need to count the instances and pass a unique value to the rest of the query Select Count(Distinct tank_id) As counttanks , Count(tank_id) As counttanks , tank_id As tank_id From garage_list RIGHT JOIN garage_list ON player_list.account_id = garage_list.account_id RIGHT JOIN garage_list ON player_list.account_id = garage_list.account_id Group By tank_id where tank_list.level='8', and player_list.clan='BAD-1' and player_list.account_id = '500549663' Quote Link to comment Share on other sites More sharing options...
hyster Posted October 5, 2015 Author Share Posted October 5, 2015 sorry copied the wrong code Select Count(Distinct tank_id) As counttanks , Count(tank_id) As counttanks , tank_id As tank_id From garage_list RIGHT JOIN player_list ON player_list.account_id = garage_list.account_id RIGHT JOIN tank_list ON player_list.tank_id = tank_list.tank_id Group By tank_id where tank_list.level='8', and player_list.clan='BAD-1' and player_list.account_id = '500549663' TABLES <garage_list> account_id :: tank_id 1234 :: 20 1234 :: 44 4321 :: 18 <tank_list> Tank_id :: name :: long_name :: row1 :: row2 :: row3 :: 20 :: sherman::usa_sher <player_list> account_id :: clan :: nickname 1234 ::bad-1:: joker 4321 ::bad-g :: grumpy Quote Link to comment Share on other sites More sharing options...
maxxd Posted October 9, 2015 Share Posted October 9, 2015 Your group by is still in the wrong spot. Move it to after the WHERE ... AND clauses. You should be getting an error when you run the query in PHPMyAdmin. Quote Link to comment 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.