-2

I have 3 tables

 1. player_list
 2. tournaments
 3. rank_list

with the following fields

player_list

- id, player_id, tournament

tournaments

- id, tournament, rl_month

rank_list

- id, player_id, rl_month, rl_position

I want to build a query to find the rl_position for a specific rl_month of each player_id from rank_list table on player_list table for a specific tournament (player_list.tournament = tournaments.id).

There is also a FK (rl_month) between tournaments and rank_list.

Any ideas?

Thanks.

Sathish
  • 4,419
  • 4
  • 30
  • 59
dimoss
  • 479
  • 1
  • 3
  • 10
  • This sounds like a normal join. Check out [this site](http://www.sitepoint.com/understanding-sql-joins-mysql-database/) for joins. also refer to [this question for multiple joins](http://stackoverflow.com/questions/3709560/mysql-join-three-tables) – LjCode Jul 24 '14 at 10:53
  • What have you already tried? – Loetn Jul 24 '14 at 11:07

2 Answers2

1

The query which made the trick alhough is a little bit slow because of the FK which are not unique is:

select r.id, r.rl_year, t.code, t.tournament, t.rl_month, 
r.rl_pos, r.rl_pts, p.player_id, p.name, p.gender, p.assoc, p.cat
from fab_plist p 
inner join fab_rl r 
on p.player_id = r.player_id
inner join fab_tournaments t 
on p.tournament = t.id where r.rl_pos is not null 
AND p.tournament = t.id AND r.rl_month = 
(select fab_tournaments.rl_month from fab_tournaments where fab_tournaments.id = t.id)
order by r.rl_pos;
dimoss
  • 479
  • 1
  • 3
  • 10
0

Try like this

select rl_position  from rank_list r 
inner join player_list p 
on p.player_id = r.player_id
inner join tournaments t p.tournament =t.id and t.rl_month='Assign month'
Sathish
  • 4,419
  • 4
  • 30
  • 59
  • Tried this but it returns records that doesn't belong to specific tournament.id on the player_list. – dimoss Jul 24 '14 at 11:36