1

I am creating a Tennis Ladder VIEW using MySQL and wish to do the following:

  • Properly create a view that includes: All player's names
  • These player names will come from the table: User - using the field: name

Here is a very simple structure of the database Tennis Ladder ERD

...and here is the view query I have so far (query generated from HeidiSQL):

select `lm`.`id` AS `id`,
`l`.`ladderName` AS `ladderName`,
`l`.`ladderType` AS `ladderType`,
`l`.`manager_id` AS `manager_id`,
`lm`.`match_round` AS `match_round`,
`lm`.`match_court_scheduled` AS `match_court_scheduled`,
`lm`.`match_datetime_scheduled` AS `match_datetime_scheduled`,
`lm`.`match_date_completed` AS `match_date_completed`,
`lm`.`match_winner` AS `match_winner`,
`lm`.`team1_user1_id` AS `team1_user1_id`,
`lm`.`team2_user1_id` AS `team2_user1_id`

from (((ladder_matches lm 
left join ladder l on((lm.ladder_id = l.id))) 
left join ladder_users lu on((lm.team1_user1_id = lu.id))) 
left join users u on((lu.user_id = u.id))) 
order by lm.id
Reno Kendo
  • 21
  • 3

1 Answers1

1

I believe I solved the issue. The query had to be adjusted slightly, but my new query retrieves the correct data

select 
`lm`.`id` AS `id`,
`l`.`ladderName` AS `ladderName`,
`l`.`ladderType` AS `ladderType`,
`mgr`.`name` AS `manager_name`,
`lm`.`match_round` AS `match_round`,
`lm`.`match_court_number` AS `match_court_number`,
`lm`.`match_datetime_scheduled` AS `match_datetime_scheduled`,
`lm`.`match_datetime_completed` AS `match_datetime_completed`,
`lm`.`match_winner` AS `match_winner`,
`lm`.`team1_user1_id` AS `team1_user1_id`,
`t1u1`.`name` AS `team1_user1_name`,
`lm`.`team1_user2_id` AS `team1_user2_id`,
`t1u2`.`name` AS `team1_user2_name`,
`lm`.`team2_user1_id` AS `team2_user1_id`,
`t2u1`.`name` AS `team2_user1_name`,
`lm`.`team2_user2_id` AS `team2_user2_id`,
`t2u2`.`name` AS `team2_user2_name` 
from ((((((`ladder_matches` `lm` 
left join `ladder` `l` on((`lm`.`ladder_id` = `l`.`id`))) 
inner join `users` `mgr` on((`l`.`manager_id` = `mgr`.`id`))) 
inner join `users` `t1u1` on((`lm`.`team1_user1_id` = `t1u1`.`id`))) 
inner join `users` `t1u2` on((`lm`.`team1_user2_id` = `t1u2`.`id`))) 
inner join `users` `t2u1` on((`lm`.`team2_user1_id` = `t2u1`.`id`))) 
inner join `users` `t2u2` on((`lm`.`team2_user2_id` = `t2u2`.`id`))) 
order by `lm`.`id`
Reno Kendo
  • 21
  • 3