count then 2 joins

[hyster]

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

[benanamen]

Your group_by is in the wrong place.

[hyster]

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'

[hyster]

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

[maxxd]

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.