Mysql – Join MySQL Lookup tables

join;MySQL

I have the following MySQL tables:

games
genres
game_genres

Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of games, I want to combine these tables and get the text genre name into a comma separated column. I'm able to GROUP_CONCAT the genres from the lookup table into a column, but struggling to make those join into the plain text version. So if a game called "Street Fighter" was associated with the Fighting and Action genres, it would pull up a row with an aliased column name of genres and would be Street Fighter, Action. I'm also using "themes" in the same manner as genres, but I'm assuming whatever method will help me do what I need to genres will work for themes too.

Here is the query I'm currently using:

SELECT
    games.*,
    GROUP_CONCAT(DISTINCT genre.genre_id) genres,
    GROUP_CONCAT(DISTINCT theme.theme_id) themes
FROM
    retro_games games
LEFT JOIN retro_game_genres genre ON games.game_id=genre.game_id
LEFT JOIN retro_game_themes theme ON games.game_id=theme.game_id
    WHERE games.game_id='1375'
    GROUP BY games.game_id

Best Answer

So I finally got this to work by using the following query:

SELECT
    games.*,
    games.game_id game_u_id,
    GROUP_CONCAT(DISTINCT genres.genre_name) genre_names,
    GROUP_CONCAT(DISTINCT themes.theme_name) theme_names
FROM
    retro_games games
LEFT JOIN retro_game_genres all_genres ON games.game_id=all_genres.game_id
LEFT JOIN retro_game_themes all_themes ON games.game_id=all_themes.game_id
LEFT JOIN retro_genres genres ON all_genres.genre_id=genres.genre_id
LEFT JOIN retro_themes themes ON all_themes.theme_id=themes.theme_id
    WHERE games.game_id='1375'
GROUP BY games.game_id

Wondering if this is the most efficient way of doing it though, or is there another method that would faster? It's fine for my one row of 1375 but it will be eventually used on thousands of rows when they view all games.

Related Solutions

Mysql – Join three tables

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>

MYSQL joining 3 tables with left join

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;