CODEWITHSUNDEEP
phpmysql
IntermediateCoder
IntermediateCoder

Reputation: 90

Searching multiple tables in MySQL database

I have several different tables in my database(mySQL).

Here are the relevant coumns for the tables

table_tournaments

  1. tournamentId
  2. tournamnetName
  3. tournamentStatus

table_tournament_results

  1. tournamentId
  2. playerId
  3. playerName
  4. playerRank
  5. tournamnetParticipants

table_players

  1. playerId
  2. playerName

The tournaments table contains the information about the tournament, the tournament results table shows the results from that table

I want to search the tournaments table by name and then with the returned results get the information from the tournament results table.

    SELECT * FROM `tournaments` `WHERE` `tournamentName` LIKE "%Query%"

I'm not sure how to go about this, maybe I need to do something via PHP, any and all help is appreciated.

Upvotes: 0

Views: 58

Answers (2)

spencer7593
spencer7593

Reputation: 108370

You can get the results you want with a join operation.

This is an example of an outer join, returning all rows from t that have the string 'foo' appearing as part of tournament_name, along with any matching rows from r.

A relationship between rows in the two tables is established by storing a common value in the tournamentId column of the two tables. The predicate in the ON clause specifies the condition that determines if a row "matches".

 SELECT t.tournamentId
      , t.tournamentName
      , t.tournamentStatus
      , r.playerId
      , r.playerName
      , r.playerRank
   FROM table_tournaments t
   LEFT
   JOIN table_tournament_results r
     ON r.tournamentId = t.tournamentId
  WHERE t.tournament_name LIKE '%foo%'
  ORDER
     BY t.tournamentId
      , r.playerId

The t and r that appear after the table names are table aliases, we can qualify references to the columns in each table by prefacing the column name with the table alias and a dot. This makes the column reference unambiguous. (In the case of tournamentId, MySQL doesn't know if you are referring to the column in t or r, so we qualify it to make it explicit. We follow this same pattern for all column references. Then, someone reading the statement doesn't need to wonder which table contains the column playerId.

Upvotes: 2

user3419778
user3419778

Reputation: 866

Your Query may be like this

SELECT a.*, b.tournamnetName FROM table_tournament_results a 
left join table_tournaments on a.tournamentId=b.tournamentId
WHERE b.tournamnetName LIKE "%Query%"

Upvotes: 0

Related Questions