Searching multiple tables in MySQL database

Question

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

Here are the relevant coumns for the tables

table_tournaments

tournamentId
tournamnetName
tournamentStatus

table_tournament_results

tournamentId
playerId
playerName
playerRank
tournamnetParticipants

table_players

playerId
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.

It is a count of how many people participated in the tournament. — IntermediateCoder
– IntermediateCoder, Commented May 13, 2015 at 18:43

spencer7593 · Accepted Answer · 2015-05-13 03:46:10Z

2

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.

edited May 13, 2015 at 3:46

answered May 13, 2015 at 3:38

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IntermediateCoder Over a year ago

What does the LEFT argument or clause do?

spencer7593 Over a year ago

The LEFT JOIN specifies an outer join operation. It says that we want to return all rows from the table on the left side (t), along with any matching rows from the table on the right side (r). If there isn't a matching row in r, the row from t (the left side) is returned, but with NULL values in place of the columns from r. If every row returned from t has at least one matching row in r, then result is the same as an inner join. We'd only see a difference where rows are "missing" from r.

user3419778 · Accepted Answer · 2015-05-13 03:44:16Z

0

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%"

answered May 13, 2015 at 3:44

user3419778

8663 gold badges8 silver badges11 bronze badges

1 Comment

spencer7593 Over a year ago

The predicate in the WHERE clause effectively negates the "outerness" of the LEFT JOIN. (The conditional test guarantees us that no rows will be returned with a NULL value for tournamentName, so any NULL-valued rows from b created by the "outer join" will be discarded, rendering this equivalent to an inner join.

Collectives™ on Stack Overflow

Searching multiple tables in MySQL database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related