NBA Basketball game lookups

Question

I have the following tables, Teams and Games. The team table includes the two letter initial of the team that includes the city and nickname as well as the team name. The game table includes the team ID for the teams in the game, the first team ID listed is the home team.

TeamID    TeamName
DM        Dallas Mavericks              
DN        Denver Nuggets                
DP        Detriot Pistons               
IP        Indiana Pacers                
MG        Memphis Grizzles              
PT        Portland Trailblazers

Game_1   Game_2   Game_3   Game_4   Game_5
IP, PT   IP, DN   IP, MG   IP, DP   IP, DM    
DM, PT   DM, MG   DM, DP   NULL     NULL
MG, DM   MG, IP   MG, DP   MG, PT    NULL

I need to be able to answer the following:

Provide the team name for those teams that the Indiana pacers played at home? (should return: Portland Trailblazers, Denver Nuggets, Memphis Grizzles, etc.)
Provide the name of team that had more than 4 home games as well as the number of home games they had (should return Indiana Pacers, 5 and Memphis Grizzles, 4).

From the first question I tried: Select TeamName From [Teams] T1 Join [Games] T2 On T1.TeamID = right(T2.Game_1,2) Select TeamID in (Select left(Game_1,2) from [Games] Where TeamID in (select TeamID from [Team] where TeamName = 'Indiana Pacers')) — Bryan, Apr 29 '18 at 17:14
What should be hurting you most is the dreadful schema of the games table. I recommend to normalize it first; it'll make your queries a lot simpler. BTW, you may want to fix the typo `GM` in the bottom games row. — Ruud Helderman, Apr 29 '18 at 17:43
Welcome to Stack Overflow! Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Apr 29 '18 at 18:51
Post the query in the question. You really have a single column in the game table with the value 'IP, PT'? — paparazzo, Apr 29 '18 at 19:06

score 0 · Answer 1 · answered Apr 29 '18 at 18:52

Do yourself a favor and first correct your design. (Or convincingly explain why you need this denormalized form.)

Drop all the Game_<number> tables. Then create a table Games with the columns HomeTeam and GuestTeam as whatever data type Teams.TeamID is and a column Number supposedly as some integer type.

Then fill Games like

+------------+-------------+------------+
| HomeTeamID | GuestTeamID | GameNumber |
+------------+-------------+------------+
| IP         | PT          |          1 |
| DM         | PT          |          1 |
| MG         | DM          |          1 |
| IP         | DN          |          2 |
| DM         | MG          |          2 |
| MG         | IP          |          2 |
                   ...
+------------+-------------+------------|

. Then the data is in a "good" form, suitable for relational processing.

(Also search and read about database normalization for further information on that.)

Your first query would then be something like

SELECT Teams.TeamName
       FROM Games
            INNER JOIN Teams
                       ON Teams.TeamID = Games.GuestTeamID
       WHERE Games.HomeTeamID = 'IP';

and the second one

SELECT Teams.TeamName,
       count(*) NumberOfGames
       FROM Games
            INNER JOIN Teams
                       ON Teams.TeamID = Games.HomeTeamID
       GROUP BY Teams.HomeTeamID
       HAVING count(*) > 4;

In your current denormalized form you'd instead had to union all your Game_<number> tables and split the list into two columns... More complicated as it needs to be.

NBA Basketball game lookups

1 Answers1