Search code examples
mysqlstringselectjoinsql-like

MySQL searching text and linking tables

I have 3 tables in my DB, tbl_user, tbl_games and tbl_usergames.

tbl_user has id and name.

tbl_games has id and name.

tbl_usergames has id_user and id_game and link tbl_user to tbl_games

I want to make a search that return users who has any game that the name match with the search string.

SELECT tbl_user.*
FROM tbl_user, tbl_usergames 
WHERE tbl_user.id IN (
 **SELECT id
 FROM tbl_games
 WHERE name LIKE '%Mario%'**
)

I tryed this but it return 0 results.

Solution 
  • SELECT u.*
FROM tbl_user u
JOIN tbl_usergames ug ON (u.id = ug.id_user)
JOIN tbl_games g ON (ug.id_game = g.id)
WHERE
    g.name LIKE '%Mario%'

    You need to join on both tables.