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

Navigation
0

I have two tables: one in which I register 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 guess of user X and if it has already been played, its result too. At the moment, the solution I found is as follows:

I select all games from round X. Then I do a while and inside it I make a new selection to find that user's guess. It is working normally, but I fear it is something wrong by the large amount of queries being made. If a round has 16 games, I once select all the games from it + 16 selections from each guess. That was the other queries on the page. Is there any leaner solution? Is it possible with two selects to just grab all the necessary info?

Edit:

There are 2 tables relevant to this question:

Games table: id - timecasa - timefora - result

Suggestions:

id - idjogo (even id of the games table) - idusuario (who made the guess) - choose (if he chose the home team or away) - result

    
asked by anonymous 20.06.2018 / 17:22

1 answer

0

Ok, you should have given us the structure of the tables, so I think you should look for something that is in more than two tables, see:

  • A table with the games
  • A table with the bet data
  • A table with the bettor data (This table is usually the table where users are registered)

    • Scheme ('Games' table) 

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

    Scheme ('bettors' table) 

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

    Scheme ('Betting' table) 

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

    Imagine this scenario and apply to your system, the way you are doing while using is the worst you can imagine, imagine if your database had millions of bettors ... Always think in extreme cases, expect the worst case and plan your application to deal with them. Let's leave it all to MySQL, it's good at it. Here's the command you'll 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

    This would return us something like this: 

    +----------------------------------+
| id |      jogo      |  apostador |
+----------------------------------+
| 1  |Jogo de Futebol | João Pedro |
+----------------------------------+
| 2  |Jogo de Futebol | José       |
+----------------------------------+
        
    20.06.2018 / 18:09
    Add Object in JCombobox [duplicate] JavaScript does not work in the plugin