Jump to content

count then 2 joins


hyster

Recommended Posts

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
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.