Jump to content

Getting statistics from 3 different tables using foreign keys


blmg2009

Recommended Posts

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. 

 

 

 

Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
  • Like 1
Link to comment
Share on other sites

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 :) 

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.