Count rows with inner joined tables

I have 3 tables:

Players:

```
mysql> SELECT * FROM players;
+-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
| player_id | team_id | player_name | player_jersey_number | player_position | player_email | player_contact_number | player_timestamp |
+-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
| 1 | 4 | Popoy Alfonso | 2 | | popoyalfonso@gmail.com | 09263453234 | 2015-08-05 00:48:10 |
| 2 | 4 | Karlo Ripas | 10 | | karloripas@yahoo.com | 09212354324 | 2015-08-05 00:50:03 |
| 3 | 4 | VHaughn Von | 32 | | von@outlook.com | 09361234565 | 2015-08-05 00:51:00 |
| 4 | 4 | Lordie Zalbahe | 23 | | lordiezalbahe@gmail.com | 09391222334 | 2015-08-05 00:52:42 |
| 5 | 4 | Jigs Selda | 8 | | jigsselda@gmail.com | 09325566323 | 2015-08-05 00:53:36 |
| 6 | 4 | Rhan Garniel | 3 | | rhangarniel@ymail.com | 09129503400 | 2015-08-05 00:54:20 |
| 7 | 5 | Johnritz Rodriguez | 11 | | johnritz@gmail.com | 09231112346 | 2015-08-05 00:56:02 |
| 8 | 5 | Garret Van Sarmiento | 7 | | garretvansarmiento@gmail.com | 09264565600 | 2015-08-05 00:56:53 |
| 9 | 5 | Lester Selda Lineses | 12 | | lesterlineses@yahoo.com | 0906

Completely rearranged answer.

I've added some data to the player and team tables to make the answers more general - see bottom of post for all DDL (CREATE TABLE tab_name...) and DML (INSERT INTO tab_name VALUES...) used in this answer. I also created the season table (unchanged from the OP's - i.e. your) original data.

BTW, welcome to the forum. But you should really have given us the DDL and DML. Take a look at the tour and also the "how to help us to help you" blog - both at the bottom left of the page. But, I got interested and did it myself, but you will get more people to help you if you provide DDL and DML.

After creating and loading the tables, I ran the following SQL.

SELECT t1.team_name,
       IFNULL(t2.num_players, 0) AS strength,
       t1.team_timestamp
FROM team t1
LEFT OUTER JOIN 
    (SELECT team_id, COUNT(team_id) AS num_players 
     FROM player 
     GROUP BY team_id
    ) t2
ON t1.team_id = t2.team_id
-- GROUP BY t1.team_id, t1.team_name, t1.season_id  -- **NOTE** - see discussion below
ORDER BY strength DESC, team_name ASC;

And the result is not quite the one you want, but close.

+----------------------+----------+---------------------+
| team_name            | strength | team_timestamp      |
+----------------------+----------+---------------------+
| Quiapo B             |        7 | 2015-08-05 00:30:25 |
| Quiapo B             |        7 | 2015-08-05 00:30:25 |
| Quiapo A             |        6 | 2015-08-05 00:30:13 |
| Quiapo A             |        6 | 2015-08-05 00:30:13 |
| Adamson Falcons      |        0 | 2015-08-05 00:31:42 |
| Balik-Balik Warriors |        0 | 2015-08-05 00:31:13 |
| Gasan Blue Eagles    |        0 | 2015-08-05 00:36:12 |
| Marikina Eagels      |        0 | 2015-08-05 00:35:11 |
| Marina Dragons       |        0 | 2015-08-05 00:32:22 |
| MDC Archers          |        0 | 2015-08-05 00:33:12 |
| Pasay Flooders       |        0 | 2015-08-05 00:32:04 |
| TIP Steallers        |        0 | 2015-08-05 00:35:32 |
+----------------------+----------+---------------------+

Running oNare's query gives (!)

+-----------+----------------+---------------------+
| team_name | num_of_players | team_timestamp      |
+-----------+----------------+---------------------+
| Quiapo A  |             26 | 2015-08-05 00:30:13 |
+-----------+----------------+---------------------+

Note that 26 = 2 * (6 + 7). But, if sql_mode is set to STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY, this query will fail with the message

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

However, a slight modification of oNare's query gives the same results as I obtained with my first query (with sql_mode not set to ONLY_FULL_GROUP_BY).

SELECT 
team.team_name, 
COUNT(player.player_id) as num_of_players,
team.team_timestamp
FROM team 
LEFT JOIN player ON (player.team_id = team.team_id)
LEFT JOIN seasons ON (seasons.season_id = team.season_id) 
GROUP BY team.team_name, team.season_id, team.team_timestamp -- **ADDED**
ORDER BY num_of_players DESC, team.team_name ASC;

Adding the GROUP BY gives the correct answer. It is interesting to note that PostgreSQL will throw an error if the GROUP BY line is commented out. My query won't work either on PostgreSQL if I don't add a GROUP BY line immediately before the ORDER BY line.

I think as a general answer, you could accept either of our answers as correct for your question (with GROUP BY) - it's not possible to obtain your desired results without more information in the tables, but see below.

As an aside, ONLY_FULL_GROUP_BY will be the default mode for 5.7, so might as well get it right now!

I think that your table structure needs some revision. You should have players for different seasons (makes sense, people transfer). You should have the same ids for the same team over different seasons - otherwise how are you going to aggregate statistics (goals/points/matches-won/lost) over many seasons?

TABLE DDL and DML - STRUCTURE and CONTENTS.

```
CREATE TABLE player (player_id INT, team_id INT, player_name VARCHAR(25));
CREATE TABLE team( team_id INT, season_id INT, team_name VARCHAR(25), team_timestamp TIMESTAMP);
CREATE TABLE season (season_id INT, season_name VARCHAR(25));

INSERT INTO player VALUES (1, 4, 'Popoy Alfonso');
INSERT INTO player VALUES (2, 4, 'Karlo Ripas');
INSERT INTO player VALUES (3, 4, 'VHaughn Von');
INSERT INTO player VALUES (4, 4, 'Lordie Zalbahe');
INSERT INTO player VALUES (5, 4, 'Jigs Selda' );
INSERT INTO player VALUES (6, 4, 'Rhan Garniel' );
INSERT INTO player VALUES (7, 5, 'Johnritz Rodriguez' );
INSERT INTO player VALUES (8, 5, 'Garret Van Sarmiento');
INSERT INTO player VALUES (9, 5, 'Lester Selda Lineses');
INSERT INTO player VALUES (10, 5, 'Laurence Lineses' );
INSERT INTO player VALUES (11, 5, 'Xandrix Buendia' );
INSERT INTO player

StackExchange Database Administrators Q#110850, answer score: 4