Getting statistics from 3 different tables using foreign keys

[blmg2009]

I have three tables in my database listed below:

 

Table:           teams_info

 

team_id         team_name         entry_year         status 

    1                   team 1                 2015                  1

    2                   team 2                 2015                  1

    3                   team 2                 2016                  1

 

Table:         team_players

 

player_id           team_id           status           members_id       position 

       1                     1                      0                       1                      1

       2                     1                      1                       2                      2

       3                     1                      1                       3                      3

       4                     1                      1                       4                      4

       5                     2                      0                       5                      1

       6                     2                      0                       6                      2

       7                     2                      1                       7                      3

 

Table:          members

 

members_id           first_name           surname            gender

       1                           joe                    blogg                 male

       2                           james               smith                 male

       3                           sarah               marshall             female

       4                           tony                 walker                 male

       5                           peter                 jones                 male

       6                           jessica              varley                 female

       7                           jane                  varley                 female

 

I'm trying to get my head around how I would get the following information, 

 

1) How many team_players from team_info.entry_year = 2015  have accepted the to join a team (this will be all players with status = 1)

 

2) How many team_players from team_info.entry_year = 2015 have been invited (basically counting all records that belong to 2015 teams)

 

3) The total Males players from 2015 teams. 

 

4) The total female players from 2015 teams.

 

i'm fairly new to SQL and it seems I have made things complicated whilst trying to follow the best standards, however I can not wrap my head around where to start. 

 

Any help would be greatly appreciated. 

 

 

 

[Barand]

First, look at the PRIMARY KEY/FOREIGN KEY relationships

                                                                                                                          
       +------------+                                          +------------+                                             
       | teams_info |                                          | members    |                                             
       +------------+                                          +------------+                                             
       | team_id    |------+                           +-------| members_id |                                             
       | team_name  |      |                           |       | first_name |                                             
       | entry_year |      |                           |       | surname    |                                             
       | status     |      |                           |       | gender     |                                             
       +------------+      |                           |       +------------+                                             
                           |                           |                                                                  
                           |                           |                                                                  
                           |     +--------------+      |                                                                  
                           |     | team_players |      |                                                                  
                           |     +--------------+      |                                                                  
                           |     | player_id    |      |                                                                  
                           +----<| team_id      |      |                                                                  
                                 | status       |      |                                                                  
                                 | members_id   |>-----+                                                                  
                                 | position     |                                                                         
                                 +--------------+                               

Those will be the JOINS in your queries, for example

FROM teams_info JOIN team_players USING (team_id)

So start by looking at the fields you need in your result and which tables you need to get those fields.

SELECT fields, needed
FROM tables needed

Then look at the conditions imposed and put those in a WHERE clause

SELECT fields, needed
FROM tables needed
WHERE conditions

If you can get it to list the correct records, you are on your way. You can then worry about how to count them.

[gizmola]

Are you using mysql or some other RDBMS?

 

I'll assume mysql here, as there are a variety of join syntaxes you can use and they vary by database.

 

What you want to do here is do an INNER join of all 3 tables which will give you one row per intersection.  

 

Fortunately, you have a good basic normalized structure that has separated the TEAM entity from the MEMBERS (players).  

 

Because your app supports "seasons" you have a good many-to-many resolver table in team_players.

 

 

So, to join the tables together, strictly speaking it doesn't really matter which table you start with, but in a case like this, I will start with the many to many resolution table:

SELECT *
 
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id)
LEFT JOIN members as m ON (m.members_id = tp.members_id)

At this point, you are inner joining and should have 1 row essentially for every match, which is going to be one row for every row in team_players.

 

In your examples, you want a count, so rather than SELECT *, you just want COUNT(*) as 'some_name'.  What you alias that name to be is up to you.  For example you could have:

SELECT count(*) as accepted_in_2015
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id)
LEFT JOIN members as m ON (m.members_id = tp.members_id)

Of course, what is missing now is the filtration.  You can add these with a WHERE clause in most cases, but I typically add them in the appropriate join criteria.  So when I'm adding a WHERE/filtration criteria that has to do with a team, I will put it in the ON that involves the teams_info join.

 

Where it's a 'member' table criteria (status in your first question) I add that there.  

 

So to answer your first question, this should probably work:

 

1) How many team_players from team_info.entry_year = 2015  have accepted the to join a team (this will be all players with status = 1)

SELECT count(*) as accepted_in_2015
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id AND ti.entry_year = 2015 AND tp.status = 1)
LEFT JOIN members as m ON (m.members_id = tp.members_id)
 

The other questions are all variations on this same blue print and can be determined the same way, however, in the case of male/female I'd probably just group by and get counts for male vs/ female in one query.

[gizmola]

Make sure you read Barand's great post before you jump right into cribbing from my SQL statements!

[gizmola]

Here's the group by example, just for reference:

SELECT m.gender, m.count(*) as countOf
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id AND ti.entry_year = 2015)
LEFT JOIN members as m ON (m.members_id = tp.members_id)
GROUP BY m.gender

[blmg2009]

First, look at the PRIMARY KEY/FOREIGN KEY relationships

                                                                                                                          
       +------------+                                          +------------+                                             
       | teams_info |                                          | members    |                                             
       +------------+                                          +------------+                                             
       | team_id    |------+                           +-------| members_id |                                             
       | team_name  |      |                           |       | first_name |                                             
       | entry_year |      |                           |       | surname    |                                             
       | status     |      |                           |       | gender     |                                             
       +------------+      |                           |       +------------+                                             
                           |                           |                                                                  
                           |                           |                                                                  
                           |     +--------------+      |                                                                  
                           |     | team_players |      |                                                                  
                           |     +--------------+      |                                                                  
                           |     | player_id    |      |                                                                  
                           +----<| team_id      |      |                                                                  
                                 | status       |      |                                                                  
                                 | members_id   |>-----+                                                                  
                                 | position     |                                                                         
                                 +--------------+                               

Those will be the JOINS in your queries, for example

FROM teams_info JOIN team_players USING (team_id)

So start by looking at the fields you need in your result and which tables you need to get those fields.

SELECT fields, needed
FROM tables needed

Then look at the conditions imposed and put those in a WHERE clause

SELECT fields, needed
FROM tables needed
WHERE conditions

If you can get it to list the correct records, you are on your way. You can then worry about how to count them.

 

 

Thank you for your explanation I have been reading upon the information you provided and it seems to make a lot more sense too me  

[blmg2009]

Are you using mysql or some other RDBMS?

 

I'll assume mysql here, as there are a variety of join syntaxes you can use and they vary by database.

 

What you want to do here is do an INNER join of all 3 tables which will give you one row per intersection.  

 

Fortunately, you have a good basic normalized structure that has separated the TEAM entity from the MEMBERS (players).  

 

Because your app supports "seasons" you have a good many-to-many resolver table in team_players.

 

 

So, to join the tables together, strictly speaking it doesn't really matter which table you start with, but in a case like this, I will start with the many to many resolution table:

SELECT *
 
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id)
LEFT JOIN members as m ON (m.members_id = tp.members_id)

At this point, you are inner joining and should have 1 row essentially for every match, which is going to be one row for every row in team_players.

 

In your examples, you want a count, so rather than SELECT *, you just want COUNT(*) as 'some_name'.  What you alias that name to be is up to you.  For example you could have:

SELECT count(*) as accepted_in_2015
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id)
LEFT JOIN members as m ON (m.members_id = tp.members_id)

Of course, what is missing now is the filtration.  You can add these with a WHERE clause in most cases, but I typically add them in the appropriate join criteria.  So when I'm adding a WHERE/filtration criteria that has to do with a team, I will put it in the ON that involves the teams_info join.

 

Where it's a 'member' table criteria (status in your first question) I add that there.  

 

So to answer your first question, this should probably work:

 

1) How many team_players from team_info.entry_year = 2015  have accepted the to join a team (this will be all players with status = 1)

SELECT count(*) as accepted_in_2015
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id AND ti.entry_year = 2015)
LEFT JOIN members as m ON (m.members_id = tp.members_id AND m.status = 1)
 

The other questions are all variations on this same blue print and can be determined the same way, however, in the case of male/female I'd probably just group by and get counts for male vs/ female in one query.

 

This is amazing, a great walk through for a newbie; Thank you very much for taking the time to help me out  

 

I will go away and study what each of sections do in more details.

[blmg2009]

SELECT count(*) as accepted_in_2015
FROM team_players as tp
LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id AND ti.entry_year = 2015)
LEFT JOIN members as m ON (m.members_id = tp.members_id AND m.status = 1)
 

The AND m.status = 1 is incorrect here as the status = 1 is in the team_players table. 

I have tried to change this too tp.status = 1; However it seems that isn't the correct way of doing this and throws an error. 

 

I assuming that this is because the tp alias will not work in the members ON(). 
 

Would I use WHERE status = 1 at the end of the mysql?

[Barand]

try

SELECT COUNT(*) as accepted
FROM team_players tp
JOIN teams_info ti 
    USING (team_id)
WHERE ti.entry_year = 2015 
    AND tp.status = 1;

+----------+
| accepted |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

[Barand]

The results for questions 2, 3 and 4 can be got from a single query

SELECT m.gender, COUNT(*) as total
FROM teams_info ti
LEFT JOIN 
    team_players tp ON ti.team_id = tp.team_id
LEFT JOIN 
    members m ON tp.members_id = m.members_id
WHERE ti.entry_year = 2015
GROUP BY gender
WITH ROLLUP ;

+--------+-------+
| gender | total |
+--------+-------+
| female |     3 |
| male   |     4 |
| NULL   |     7 |   <--- ROLLUP gives the total of the subtotals
+--------+-------+
3 rows in set (0.00 sec)

[gizmola]

In my queries I screwed up looking at the tables, and should have removed the references to m.status = 1, and had 2 criteria in the other ON clause:

 

 

 

LEFT JOIN teams_info as ti ON (ti.team_id = tp.team_id AND ti.entry_year = 2015 AND ti.status = 1)

 

As I mentioned, there are frequently different ways to do the same thing, and Barand actually tested things out for you, where I didn't, but he shows the option of using the WHERE clause for filtration.