Mysql – Join three tables

join;MySQL

I have those three tables

SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field;
+---------------+------------------------+
| Occupation Id | Occupation Description |
+---------------+------------------------+
|             1 | Software Engineer      |
|             2 | Economics              |
|             3 | Structural Engineer    |
|             4 | Legal Advisors         |
|             5 | Plumbers               |
|             6 | Social Advisors        |
|             7 | Musicians              |
+---------------+------------------------+

SELECT usrId AS 'User Id',usrName AS 'User Name',usrOccuId AS 'User Occupation Id' FROM users;
+---------+------------+--------------------+
| User Id | User Name  | User Occupation Id |
+---------+------------+--------------------+
|       2 | goodys     |                  6 |
|       5 | realmadrid |                  7 |
|       6 | petousis   |                  6 |
+---------+------------+--------------------+

SELECT pstId AS 'Post Id',pstTitle AS 'Post Title',pstOccuId AS 'Post Occupation Id' FROM job_post ORDER BY pstId;
+---------+-------------------------------------------+--------------------+
| Post Id | Post Title                                | Post Occupation Id |
+---------+-------------------------------------------+--------------------+
|       4 | Software Engineer Recruit                 |                  1 |
|       5 | Web Developer Recruit                     |                  1 |
|       6 | Saxophonist                               |                  7 |
|       7 | Construction Company looking for plumber. |                  5 |
|       8 | Economic Analyst                          |                  2 |
|       9 | Legal Advisor                             |                  4 |
|      10 | Economic Assistant                        |                  2 |
+---------+-------------------------------------------+--------------------+

I want to combine the below two tables in order to have one with the Occupation Descriptions,Post per Job and Users per Job.

SELECT occuDscr job,count(pstOccuId) AS 'Posts' FROM job_post INNER JOIN occupation_field on pstOccuId = occuId GROUP BY job;
+-------------------+-------+
| job               | Posts |
+-------------------+-------+
| Economics         |     2 |
| Legal Advisors    |     1 |
| Musicians         |     1 |
| Plumbers          |     1 |
| Software Engineer |     2 |
+-------------------+-------+

SELECT occuDscr job,count(usrOccuId) AS 'Users' FROM users INNER JOIN occupation_field on usrOccuId = occuId GROUP BY job;
+-----------------+-------+
| job             | Users |
+-----------------+-------+
| Musicians       |     1 |
| Social Advisors |     2 |
+-----------------+-------+

Something like this

    +-------------------+-------+-------+
    | job               | Posts | Users |
    +-------------------+-------+-------+
    | Economics         |     2 |     0 |
    | Legal Advisors    |     1 |     0 |
    | Musicians         |     1 |     1 |
    | Plumbers          |     1 |     0 |
    | Software Engineer |     2 |     0 |
    | Social Advisors   |     0 |     1 |
    +-------------------+-------+-------+

Best Answer

I essentially performed a LEFT JOIN of the occupation_field table against both of your queries, joining by means of the occuDscr field. Here is your new query:

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    USING (occuDscr)
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    USING (occuDscr)
;

If the USING clause is not clear to you, here is your without the USING clause

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    ON oc.occuDscr = postInfo.occuDscr
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    ON oc.occuDscr = userInfo.occuDscr
;

Here is your sample data loaded:

mysql> drop database if exists giannosfor;
Query OK, 3 rows affected (0.13 sec)

mysql> create database giannosfor;
Query OK, 1 row affected (0.00 sec)

mysql> use giannosfor
Database changed
mysql> create table occupation_field
    -> (occuId int not null auto_increment,
    -> occuDscr varchar(40),
    -> primary key (occuId));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into occupation_field (occuDscr) values
    -> ('Software Engineer'),('Economics'),('Structural Engineer'),
    -> ('Legal Advisors'),('Plumbers'),('Social Advisors'),('Musicians');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create table users
    -> (usrId int not null,
    -> usrName varchar(20),
    -> usrOccuId int not null);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into users values
    -> (2,'goodys',6),(5,'realmadrid',7),(6,'petousis',6);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table job_post
    -> (pstId int not null,pstTitle varchar(60),
    -> pstOccuId int not null,
    -> primary key (pstId));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into job_post values
    -> ( 4,'Software Engineer Recruit',1),
    -> ( 5,'Web Developer Recruit',1),
    -> ( 6,'Saxophonist',7),
    -> ( 7,'Construction Company looking for plumber.',5),
    -> ( 8,'Economic Analyst',2),
    -> ( 9,'Legal Advisor',4),
    -> (10,'Economic Assistant',2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from occupation_field;
+--------+---------------------+
| occuId | occuDscr            |
+--------+---------------------+
|      1 | Software Engineer   |
|      2 | Economics           |
|      3 | Structural Engineer |
|      4 | Legal Advisors      |
|      5 | Plumbers            |
|      6 | Social Advisors     |
|      7 | Musicians           |
+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from users;
+-------+------------+-----------+
| usrId | usrName    | usrOccuId |
+-------+------------+-----------+
|     2 | goodys     |         6 |
|     5 | realmadrid |         7 |
|     6 | petousis   |         6 |
+-------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from job_post;
+-------+-------------------------------------------+-----------+
| pstId | pstTitle                                  | pstOccuId |
+-------+-------------------------------------------+-----------+
|     4 | Software Engineer Recruit                 |         1 |
|     5 | Web Developer Recruit                     |         1 |
|     6 | Saxophonist                               |         7 |
|     7 | Construction Company looking for plumber. |         5 |
|     8 | Economic Analyst                          |         2 |
|     9 | Legal Advisor                             |         4 |
|    10 | Economic Assistant                        |         2 |
+-------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)

mysql>

Here is the execution of the first query

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

Here is the execution of the second query

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     ON oc.occuDscr = postInfo.occuDscr
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     ON oc.occuDscr = userInfo.occuDscr
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2012-01-11 11:50 EDT

To remove Jobs with no Posts, change the first LEFT JOIN into INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
5 rows in set (0.00 sec)

mysql>

To remove Jobs with no Users, change the second LEFT JOIN into INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------------+-------+-------+
| job             | Posts | Users |
+-----------------+-------+-------+
| Musicians       |     1 |     1 |
| Social Advisors |     0 |     2 |
+-----------------+-------+-------+
2 rows in set (0.00 sec)

mysql>

To remove Jobs with no Users or no Posts, change the both LEFT JOINs into INNER JOINs:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------+-------+-------+
| job       | Posts | Users |
+-----------+-------+-------+
| Musicians |     1 |     1 |
+-----------+-------+-------+
1 row in set (0.00 sec)

mysql>

UPDATE 2012-01-11 12:03 EDT

To remove Jobs with no Post or Users, add a WHERE clause to check if either cound is greater than zero:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> WHERE
    ->     IFNULL(postInfo.PostCount,0)>0 OR
    ->     IFNULL(userInfo.UserCount,0)>0
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Social Advisors   |     0 |     2 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
6 rows in set (0.00 sec)

mysql>
Related Question