SQL group by, pass value to a join to get other data

Question

I'm trying to get the count from garage_list:account_id get 1 instance from there and get the corresponding player_list:account_id also garage_list:tank_id to tank_list:tank_id

end table will look like:

tank :: count (alt text on count for players)

IS-3 :: 2 --> ( player 1, player 3)

E-100 :: 3 --> (player1, player 4, player 9)

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

it will be vertical as depending on an option for level I could end up with 60 odd results — Lance Rilloil, Oct 05 '15 at 18:51
Your end result does not correspond to the sample data supplied. — domenicr, Oct 05 '15 at 18:55

score 0 · Answer 1 · answered Oct 05 '15 at 19:07

So from what I gather you would want something like this:

select pc.NumOfPlayers, tl.tank_id, pl.account_id, pl.clan, pl.nick
  from tank_list tl
  inner join garage_list gl
             on  tl.tank_id = gl.tank_id
  inner join player_list pl
             on gl.account_id = pl.account_id
  inner join (select tank_id, 
                     count(*) as NumOfPlayers 
                from garage_list group by tank_id) as pc
              on tl.tank_id = pc.tank_id
order by tl.tank_id

Check out SQL Fiddle

SQL group by, pass value to a join to get other data

1 Answers1