0

This questions is similar to what I need but the answer seems rather specific to the question rather than a general answer. One to many relationship query result in a single row and dynamic multiple columns

I have a table of games (we will call this table matches)

gameID     Home Team     Away Team     DateTime     
-----    -----------    ----------     ----------    
 1        Barcelona     Real Madrid    12-08-21 10:00:00  
 2        Valencia      Sevilla        12-08-21 10:00:00  

Each game has potentially several attributes, half time score, full Tim score, extra time score, penalty score, attendance etc.

This is stored in a one to many table which looks like (we will call this match attributes)

id       game id     attribute id     attribute value          
-----    --------   -----------     -----------------        
 1           1            1                3    
 2           1            2                3 
 3           1            5                32123
 4           2            1                4    
 5           2            2                0 

Where attribute id is a foreign key for this table (we will call this attributes)

attribute id     attribute name           
----------      ---------------          
    1           Home Team Extra Time Score    
    2           Away Team Extra Time Score
    5           Game Attendance
    8           Home Team Penalty Score 
    5           Away Team Penalty Score

At the moment when I want to show a match to a user I query the matches table and separately query the match attributes table, ideally however I could make one join that merges the several results from the match attributes table into columns of the matches table. Here are two examples.

Querying 1 game (Where gameId = 1)

gameID     Home Team     Away Team     DateTime            Home Team Extra Time Score     Away Team Extra Time Score         Attendance
-----    -----------    ----------     ----------          ----------------------------    ----------------------------    ----------
 1        Barcelona     Real Madrid    12-08-21 10:00:00            3                                     3                     32123

Querying multiple games (game id = 1 or 2)

gameID     Home Team     Away Team     DateTime            Home Team Extra Time Score     Away Team Extra Time Score         Attendance
-----    -----------    ----------     ----------          ----------------------------    ----------------------------    ----------
 1        Barcelona     Real Madrid    12-08-21 10:00:00            3                                     3                     32123
 2        Valencia     Sevilla         12-08-21 10:00:00            4                                     0                    null

How would this be possible, I don't even know what functionality I could use to do this, as I don't think joins would work with multiple rows like this

7
  • From what I can see, this is all about how to present information. I would suggest doing that in the presentation layer of the application. Jun 29, 2021 at 5:49
  • Thats what I am doing at the moment, but is it faster to do a query over matches and match attributes rather than just 1 query that joins the results?
    – Marc-9
    Jun 29, 2021 at 5:55
  • It's called a pivot (possibly a dynamic one) Jun 29, 2021 at 15:25
  • Thanks @Charlieface reading up on pivot tables, shows that this does seem to be what I want, once I figure these out ill write up an answer to my question above. Thanks a ton!
    – Marc-9
    Jun 29, 2021 at 20:03
  • EAV schema is fraught with problems. The query probably needs some "self joins". It would probably be better to have columns rather than rows for all the attributes.
    – Rick James
    Jun 30, 2021 at 7:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.