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.
    – Lennart
    Jun 29 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 at 5:55
  • It's called a pivot (possibly a dynamic one) Jun 29 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 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 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.