CODEWITHSUNDEEP
mysqlsql
Evgeny Gil
Evgeny Gil

Reputation: 327

Where clause inside select with subquery

Have 6 tables

tours_atp (_wta - the same)

players_atp (_wta - the same) enter image description here

games_atp (_wta - the same)

enter image description here

With tours_atp + games_atp join everything OK, but how i can join (or inside select) players table for recieve columns with players names inside, something like this

ID1_G, ID2_G, player1, player2, id_t.....

I try to select inside join

SELECT DISTINCT `ID_T_G` AS `id_t`, `NAME_T` AS `name`, `DATE_G`, `RESULT_G`, `DATE_T`, `COUNTRY_T`, `ID1_G`, `ID2_G`, `ID_R_G`,
  (SELECT CONCAT(`NAME`, ' ', `LASTNAME`) AS player1 FROM players_ru_atp WHERE games_atp.ID1_G = players_ru_atp.ID_P) AS `player1`,
  (SELECT CONCAT(`NAME`, ' ', `LASTNAME`) AS player2 FROM players_ru_atp WHERE games_atp.ID2_G = players_ru_atp.ID_P) AS `player2`
FROM `games_atp` LEFT JOIN `tours_atp` ON tours_atp.ID_T = games_atp.ID_T_G
WHERE (`DATE_G` BETWEEN '2018-06-29' AND '2018-06-29')
      AND (`player1` LIKE '%John Doe%') GROUP BY `ID_T_G`;

but Unknown column 'player1' in 'where clause'

Upvotes: 0

Views: 56

Answers (1)

Eric
Eric

Reputation: 3257

Try this. But your GROUP BY doesn't make sense.

SELECT DISTINCT `ID_T_G` AS `id_t`
    , `NAME_T` AS `name`
    , `DATE_G`, `RESULT_G`
    , `DATE_T`, `COUNTRY_T`
    , `ID1_G`, `ID2_G`
    , `ID_R_G`
    , (SELECT CONCAT(`NAME`, ' ', `LASTNAME`) AS player1 FROM players_ru_atp WHERE games_atp.ID1_G = players_ru_atp.ID_P) AS `player1`
    , (SELECT CONCAT(`NAME`, ' ', `LASTNAME`) AS player2 FROM players_ru_atp WHERE games_atp.ID2_G = players_ru_atp.ID_P) AS `player2`
FROM `games_atp` 
LEFT JOIN `tours_atp` ON tours_atp.ID_T = games_atp.ID_T_G
WHERE `DATE_G` BETWEEN '2018-06-29' AND '2018-06-29'
      AND (SELECT CONCAT(`NAME`, ' ', `LASTNAME`) AS player1 FROM players_ru_atp WHERE games_atp.ID1_G = players_ru_atp.ID_P) LIKE '%John Doe%'
GROUP BY `ID_T_G`;

Upvotes: 1

Related Questions