How to select rows that match one of the columns in different tables?

Asked

Viewed 44 times

0

I have two tables: one that registers the games of the round and the other that registers the guesses for these games. I want to show a table where the Game appears, the user’s guess X and if it has already been played, its result as well. At the moment, the solution I found is the following:

I select all the games of round X. Then I do a while and within it I make a new selection to find the guess of that user. It’s working normally, but I’m afraid there’s something wrong with the large amount of queries being made. If a round has 16 games, I select once all of her games + 16 selections from each guess. That’s the other page queries. Is there a more streamlined solution? It is possible with two selects to just pick up all necessary information?

Edit:

There are 2 tables relevant to this question:

Table Games: id - timecasa - timefora - result

Tabela Palpites:

id - idjogo (same as table id games) - idusuario (from whom you guessed) - choose (whether you chose home or away team) - result

php mysql

  • What do you want is a JOIN, the guesses are from all users or just one? Put your bank structure, where are the results?

1 answer

0

Okay, you should have given us the structure of the tables, so it seems to me you should look for something that is in more than two tables, see:

  1. A table with the games
  2. A table with the bet dice
  3. A table with the bettor’s data(This table is usually the table where users are registered)

Scheme(Table 'games')

+----------------------
|       jogos         |
+---------------------+
| id | desc_jogo      |
+---------------------+
| 1  | Jogo de Futebol|
+---------------------+
| 2  | Jogo de Peteca |
+---------------------+

Scheme(Betting table')

+----------------------
|    apostadores      |
+---------------------+
| id | nome           |
+---------------------+
| 1  | João Pedro     |
+---------------------+
| 2  | José           |
+---------------------+

Scheme(Table 'bets')

+--------------------------------+
|            apostas             |
+--------------------------------+
| id | jogos_id | apostadores_id |
+--------------------------------+
| 1  |    1     |       1        |
+--------------------------------+
| 2  |    1     |       2        |
+--------------------------------+

Imagine this scenario and apply in your system, the way you are doing using while is the worst you can imagine, imagine if your database had millions of gamblers... Always think extreme cases, expect the worst case and plan your application to deal with them.Let’s leave it all to Mysql, he’s good at it. See the command you will use:

SELECT apostas.id AS id, jogos.desc_jogo AS jogo, apostadores.nome AS apostador FROM apostas INNER JOIN jogos_id ON jogos_id = jogos.id INNER JOIN apostadores ON apostadores_id = apostadores.id WHERE jogos_id = 1

It would bring back something like this:

+----------------------------------+
| id |      jogo      |  apostador |
+----------------------------------+
| 1  |Jogo de Futebol | João Pedro |
+----------------------------------+
| 2  |Jogo de Futebol | José       |
+----------------------------------+

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.