Click here to Skip to main content
15,867,453 members
Articles / Database Development

Understanding Complex MySQL JOIN

Rate me:
Please Sign up or sign in to vote.
4.50/5 (8 votes)
21 Nov 2013CPOL13 min read 64.8K   700   13   6
Fetching complex data from multiple table using nested MySQL queries, JOIN operations, LIKE operator and aggregate functions(eg. GROUP BY, GROUP_CONCAT, etc.)

Introduction

The purpose of this article is to show the power of MySQL JOIN operations, Nested MySQL Queries(intermediate or temporary result set table) and Aggregate functions(like GROUP BY). One can refer to link1 or link2 for basic understanding of MySQL join operations.

In order to explain various MySQL JOIN operations, I have chosen a problem domain of an idea management system. Let me first briefly describe this problem domain.

  • System users: In this idea management system, there are set of of registered users who belongs to different departments. Basic requirement of this system is to keep track of all the ideas submitted by registered users.
  • Idea: A single idea can originates from single or multiple users, so an idea can have single or multiple innovators accordingly. However, only one innovator can submit the idea and other can be listed as idea innovators.
  • User role: User can have multiple role like normal user or admin user. Admin user can change the status of submitted idea from pending status(0) to accepted(1) or rejected(2).
  • Idea status update: On status change, all the associated innovators receive some points as reward.
  • Idea events generation: Based on idea progress, some events can be generated which are then fed into system as idea events by admin user.
  • Generation of consolidated reports: Main objective of this idea management system is to process the user & idea information to show consolidated information like list of recent ideas, list of top users or top groups based on points to motivate users to submit more ideas.
In this article, I will explain, how to get this consolidated information in a single complex MySQL query using MySQL JOIN and Aggregate operators. I would suggest, first attempt the problems mentioned in this article yourself before looking into the actual MySQL queries in order to get better understanding.

This system contains following tables which we will be referring through out this article to solve the problems. For sample data, download the database dump and import it into your MySQL environment(see using the code section).

Let me first start with brief description of these tables.

  • user_info:
     id   full_name    dep_id    points  
    jack JACK D. 2 2
    jackson M.S. Jackson 3
    alice Alice W 2 1
    bob Bob S. 2 2

    This table list information of all registered users.

    Attributes:

    • id :- Auto generated id for user
    • full_name :- Full name of user
    • dep_id :- Department id of user which is a foreign key referring to 'id' field in dept_info table
    • points :- Total points received by user through idea submission 

  • user_idea:
     idea_id   user_id   title  innovators  idea_categories    status   description
    1 jack Video Annotations jack 1;2 1 Video Annotations Description
    2 jack Optimize waterfall model jack;jackson 3 0 Optimize waterfall model Description
    3 jackson Automation jackson 1 1 Automation Description
    4 jackson Design Patterns jackson 1 0 Design Patterns Description
    5 alice Identify Video Objects alice;jack 2 1 Identify Video Objects Description
    6 bob Tin Can LMS bob 1 1 Tin Can LMS Description
    7 bob Text Summarization bob 2;3 0 Text Summarization Description

    This table contains details of all the ideas submitted by registered users present in user_info table.

    Attributes:

    • idea_id :- Auto generated id for submitted idea
    • user_id :- id of user who submitted the idea. It is a foreign key referring to 'id' field of user_info table
    • title :- title of idea
    • innovators :- Semicolon concatenated list string of all the user 'id's who invented this idea. For example, innovators ="1;2" means, user with id=1(i.e. jack) and user with id =2(i.e. jackson) are innovators of this idea. 
    • idea categories :- Semicolon concatenated list string of category 'id's (referring to category_id in idea_categories table) of all idea categories in which idea can belong. For example, idea_categories ="2;3" means idea belong to category with category_id =2 (i.e. Video) and category with category_id =3(i.e. Language Analysis).
    • status :- acceptance status of idea (e.g. 0 implies accepted, 1 implies accepted & 2 implies rejected)
    • description :- description of idea

  • idea_events:
     idea_id    event_id    events 
    Ideation Phase
    Implementaion Phase
    Discussion Phase

    It contains event information associated with idea present in user_idea table.An idea can have zero or multiple idea events.

    Attributes:

    • idea_id :- Foreign key referring to 'idea_id' field of user_idea table
    • event_id :- Auto generated id for event
    • events :- Event description string

  • idea_categories:
    category_id    category_name 
    Project Lifecycle 
    Video
    Language Analysis

    This table contain information of all registered idea categories in which any submitted idea can belong.

    Attributes:

    • category_id :- Auto generated id for category
    • category_name :- Category Name

  • dept_info:
     id   name 
    Other
    Development
    Manager

    This table list all the user departments.

    Attributes:

    • id :- Auto generated id for department
    • name :- Department Name

Using the code

Download & extract the source code. Import the sql_join.zip database into your MySQL environment.

mysql -u root < {path-of-extracted-folder}\sql_join.txt
You can also use online SQL practice tool like http://sqlfiddle.com/ to build your database online and execute query on top of it. Just copy the contents of 'sql_join_sqlfiddle.txt' into left panel of sqlfiddle page and click "Build Schema" button. After schema is built, you can type in your MySQL queries in the right panel and click "Run SQL" button to see the result displayed in bottom panel.

Ok, lets try to get some consolidated information from our system starting with simpler one to explore power of inner join and inner queries.

Problem 1: Getting list of ideas with user information 

Task: In this problem, we want to retrieve all the ideas with attributes (idea_id, title, status and innovators) which are present in user_idea table along with idea user information with attributes (user id and user full name) present in user_info table. In order to get desired result, we have to join user_idea and user_info tables.

Concepts: Inner Join, Table aliases 

Relevant Tables:user_info and user_idea

Expected Result:

 idea_id   user_id    full_name   title  innovators   status
jack  JACK D.  Video Annotations jack 1
jack  JACK D.  Optimize waterfall model  jack;jackson 0
jackson M.S. Jackson Automation jackson 1
4 jackson M.S. Jackson Design Patterns jackson 0
5 alice Alice W Identify Video Objects alice;jack 1
6 bob Bob S. Tin Can LMS bob 1
7 bob Bob S. Another Idea1 bob 0

Solution:

Here is the appropriate query :
SQL
SELECT UI.idea_id, UI.user_id, UInfo.full_name, UI.title, UI.innovators, UI.status
FROM user_idea AS UI 
INNER JOIN user_info AS UInfo ON UI.user_id = UInfo.id

Note: Here, we have used table aliases using "AS" to refer table fields more easily in a convenient way.

This was a simple example of Inner Join usage. Here, records from both user_idea and user_info table are merged based on common user_id.

Before moving to complex queries, let's try to understand SQL 'LIKE' operator which is primarily used to test if one string is a part of another string.

Problem 2: Fetch all accepted ideas for a specific user

Task: Here, we want to get list of all accepted ideas(i.e. idea with status = 1) for a particular user who is one of the innovators of those ideas.

Concepts: Use of Like operator to check if a given string is part of list represented as string where list elements are joined using some separator(e.g ';').

Relevant Tables: user_idea

Expected Result:

 idea_id    user_id    title  innovators  
1 jack Video Annotations jack
5 alice Identify Video Objects alice;jack

Solution:

Approach 1:
SQL
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND UI.innovators like '%jack%';

Result:

 idea_id   user_id   title  innovators 
1 jack Video Annotations jack
3 jackson Automation jackson
5 alice Identify Video Objects alice;jack

Issues: If you examine this query carefully, you can realize that it might fetch wrong results if one user_id is substring of another. For e.g. above query will return idea 3 having innovators "jackson"(as jackson contain jack) which is not desired. This approach might be suited in situations where each user id is distinct and doesn't contain other user id as substring.

Approach 2: Append list separator(';') to the start and end of innovators string before using LIKE operator. It's kind of tricky way of matching user_id without using multiple'OR' operator in 'LIKE" statements to handle cases where innovator present at the start, middle or end of the innovators string.

Case 1: Appending list separator:

SQL
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI  
WHERE UI.status=1 AND CONCAT(';',UI.innovators,';') like '%;jack;%';
Alternatively, we could use following approaches to get the same result but, I prefer the preceding approach as it is more concise and faster.

Case 2: Using Multiple 'OR'

SQL
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND 
( 
UI.innovators = 'jack'		OR	/* e.g. innovators = 'jack' ; only one user is present in the innovators list*/
UI.innovators like 'jack;%' 	OR 	/* user id is present at the start. e.g. innovators = "jack;bob" */
UI.innovators like '%;jack;%'	OR 	/* user id is present in the middle. e.g. innovators = "alice;jack;bob" */
UI.innovators like '%;jack'   		/* user id is present at the end. e.g. innovators = "alice;jack" */
)

Case 3: Using Regular expression (REGEXP or RLIKE)

SQL
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND UI.innovators REGEXP '^jack$|^jack;|;jack;|;jack$';

Case 4: Create function in the database incase this operation is used frequently

SQL
//Create Function isMember
DELIMITER $$
CREATE FUNCTION IsMember(inList Text, inMember varchar(10))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE exp varchar(50) DEFAULT '';
  SET exp = CONCAT('^',inMember,'$','|^',inMember,';|;','inMember',';|;',inMember,'$');
RETURN inList REGEXP exp;
END
$$
DELIMITER ;

//Using Function
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND IsMember(UI.innovators,'jack');

Performance Analysis: In General 'LIKE' is more optimized than 'REGEXP'(function in this case) for large dataset. Below is the approximate execution time for queries against set of approx 70K user_ideas.

System Details:

Processor: (Intel Core i5 CPU M520 @2.4GHz 2.4GHz), OS: (Windows 7, 4GB RAM), MySQL Version: (5.5.24 for Win32 (x86)).

Query Execution Time:

  • Case1 (CONCAT with LIKE): ~0.28sec
  • Case2 (Multiple LIKE with OR): ~0.29sec
  • Case3 (REGEXP): ~0.9sec
  • Case4 (FUNCTION): ~8.6sec

Result:

 idea_id    user_id    title   innovators  
1 jack Video Annotations jack
5 alice Identify Video Objects alice;jack

Problem 3: Fetch idea details with idea categories names and idea events information

Task: This problem is little bit complex as it involves fetching concatenated data(e.g. idea events) from one table(e.g idea events table) and merging it with row fetched from other table(e.g. idea table)

Concepts: LEFT JOIN, INNER JOIN, Nested SQL query, Table aliases and Aggregate Functions like GROUP BY, GROUP_CONCAT

Relevant Tables: user_idea, idea_events, idea_categories

Expected Result:

 idea_id    title   events   categories
Video Annotations Ideation Phase;Implementation Phase Project Lifecycle;Video
2 Optimize waterfall model NULL Language Analysis
3 Automation Discussion Phase Project Lifecycle
4 Design Patterns NULL Project Lifecycle
5 Identify Video Objects NULL Video
6 Tin Can LMS NULL Project Lifecycle
7 Text Summarization NULL Video;Language Analysis

Solution:

We can divide our problem in two parts. First, we will fetch all the events associated with each idea and merge into single concatenated field(named as 'events') using some separator(say ';'). Second, we will join the result of this query with idea_categories table to add idea category information.

Note: Idea events can be null. i.e. some idea may not have any associated event.

Step 1: Query to fetching Idea Events (concatenated using separator ';' )

Approach 1:
SQL
SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events
FROM user_idea AS UI
INNER JOIN idea_events AS IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id

Note: GROUP BY and GROUP_CONCAT aggregate operators are used to club all events specific to single idea.

Result:

 idea_id   title   events
1 Video Annotations Ideation Phase;Implementation Phase
3 Automation Discussion Phase

Issues: Ideas with no associated event are not present in results as INNER JOIN is used. We could resolve this problem in approach2  using LEFT JOIN.

Approach 2:

SQL
SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events
FROM user_idea UI
LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
GROUP BY UI.idea_id

Result:

 idea_id   title   events
1 Video Annoatations Ideation Phase;Implementation Phase
2 Optimize waterfall model NULL
3 Automation Discussion Phase
4 Design Patterns NULL 
Identify Video Objects NULL
6 Tin Can LMS NULL
7 Text Summarization  NULL 

Note:

  • We have used LEFT JOIN instead of INNER JOIN as we want all records of ideas even if there is no corresponding event.
  • We are using GROUP_CONCAT and GROUP_BY to club events per idea in a single entry. Without GROUP_BY & GROUP_CONCAT operators, we would get multiple entries per idea as shown below (see first two row of result):
    SQL
    SELECT UI.idea_id, UI.title, IE.events
    FROM user_idea UI
    LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id;
    

    Result(Without GROUP_BY):

     idea_id   title   events
    1 Video Annoatations Ideation Phase
    1 Video Annoatations Implementation Phase
    2 Optimize waterfall model  NULL 
    ... ... ...

    However, if we use GROUP_BY without using GROUP_CONCAT, we won't get expected result. We will get one row per idea as we are grouping by idea_id with single event information that corresponds to that idea(missing other events) as we are not clubbing events using GROUP_CONCAT (see event column of row 1 in result. Only 'Ideation Phase' is coming and not the 'Implementation Phase'). Key rule is, one should use aggregate operators like GROUP_CONCAT, AVG, SUM, COUNT, MAX, MIN etc. whenever using GROUP_BY.

    SQL
    SELECT UI.idea_id, UI.title, IE.events
    FROM user_idea UI
    LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
    GROUP BY UI.idea_id;
    

    Result(Without GROUP_CONCAT):

     idea_id    title   events 
    Video Annoatations  Ideation Phase
    2 Optimize waterfall model NULL
    3 Automation Discussion Phase
    ... ... ...

Step 2: Fetch Pillars Information by joining idea_categories tables with the results of query in Step1.  

We will use SQL query in step 1 as inner or nested query for fetching category information.
SQL
SELECT AUI.idea_id, AUI.title, AUI.events,
       GROUP_CONCAT(IC.category_name SEPARATOR ';') as categories  
FROM (       
	SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events ,
	       CONCAT(';',UI.idea_categories,';') as temp_categories
 	FROM user_idea UI 
	LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id      
	GROUP BY UI.idea_id
) AS AUI
INNER JOIN idea_categories IC
ON AUI.temp_categories LIKE CONCAT('%;',IC.category_id,';%') 
GROUP BY AUI.idea_id;

Result:

 idea_id   title   events   categories
1 Video Annotations Ideation Phase;Implementation Phase Project Lifecycle;Video
2 Optimize waterfall model NULL Language Analysis
3 Automation Discussion Phase Project Lifecycle
4 Design Patterns NULL Project Lifecycle
Identify Video Objects  NULL  Video
Tin Can LMS  NULL  Project Lifecycle
Text Summarization  NULL  Video;Language Analysis

Note: 

  • We have used nested query result set aliased as AUI to append category information. In general, nested query of following pattern are very useful when you want to peform join operation on the result set of some temporary or intermediate sql query.
    SQL
    SELECT T1.field1,..., NT.field1,...
    FROM (
        SELECT T2.field1, ..
        FROM T2
        WHERE ...
    ) AS NT /* Nested Query Result */
    INNER JOIN T1 ON T1.someField = NT.someField && ....
    WHERE ...
    
  • We have applied techniques mentioned in Problem 2(Approach2-Case1) to get category names (this time we have used same technique in SELECT statement). However, we could use the same in conditional clause also). In nested query, we are appending list separator(';') to the user_idea.categories field and aliasing it as 'temp_categories' which will be used in conditional clause of outer query. The result set of nested query will have 4 columns i.e. 'idea_id', 'title', 'events' (concatenated event string) and 'temp_categories'. Now, this result set is used as temporary table aliased as 'AUI' which is then joined with idea_categories table using LIKE condition on temp_categories. GROUP_BY & GROUP_CONCAT are then used in outer SELECT query to get concatenated string of idea category names.
  • Alternate way to get the same result could be, get idea events & idea categories separately and use inner join or intersection. 

Problem 4: Get Top Innovator Groups based on some heuiristic like group point average.

Task: In this problem, we want to fetch leading or top groups information with attributes like total number of innovators in a leading group, total number of ideas submitted by innnovators in that group and total group points. Also, sort the result based on group point average.

Concepts: COUNT, SUM, GROUP BY, ORDER BY,DESC, Nested SQL query, JOIN

Relevant Tables: user_info, user_idea, dept_info 

Expected Result:

 dept_id   dept_name   totalInnovators    totalIdeas    totalPoints  
3 Manager 1 2 3
2 Development 3 5 5

Solution:

We will divide this task also into two parts as done for Problem 3. First, we will join the user_info with dep_info to get user department or group information and join it with idea table to get total idea count submitted by each user. Second, we will join the result of previous step with dept_info to get total number of innovators, total ideas, total points per group using aggregate operators sorted by group point average

Step 1: Get user Department infomation joining user_pre_info and dept_info and count user ideas

SQL
SELECT UPI.id as user_id, UPI.dept_id, DI.name as dept_name, UPI.points,                          COUNT(UI.idea_id) AS totalIdeas 
FROM user_info UPI 	
INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id 
LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id 
GROUP BY UPI.id

Result:

 user_id   dept_id    dept_name   points   totalIdeas 
alice 2 Development 1 1
bob 2 Development 2 2
jack 2 Development 2 2
jackson 3 Manager 3 2

Note:

  • We have used COUNT operator to get idea count per user
  • We have used GROUP BY operator to group based on user_id

Step 2: Count number of users, total ideas in a group and sort result by point average w.r.t number of users in a group

We will join the result of SQL query in step 1 with dept_info table to get the desired result.
SQL
SELECT UGI.dept_id, UGI.dept_name, COUNT(UGI.id) AS totalInnovators,                              SUM(UGI.totalIdeas) AS totalIdeas, SUM(UGI.points) AS totalPoints 
FROM ( 
        SELECT UPI.id, UPI.dept_id, DI.name as dept_name, UPI.points,                                     COUNT(UI.idea_id) AS totalIdeas 
	FROM user_info AS UPI 
	INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id 
	LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id 
	GROUP BY UPI.id
) AS UGI 
GROUP BY UGI.dept_id HAVING (totalPoints>0 AND totalInnovators>0) ORDER BY SUM(UGI.points)/COUNT(UGI.id) DESC LIMIT 5

Result:

 dept_id   dept_name   totalInnovators   totalIdeas   totalPoints 
3 Manager 1 2 3
2 Development 3 5 5

Note:

  • we have grouped the result by department id to get total points per group.
  • We have sorted the result by group point average using ORDER BY.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
BugHTML in code sample Pin
Duane Mitchell31-Dec-16 7:40
Duane Mitchell31-Dec-16 7:40 
SuggestionFlaw in Table Design Pin
Peter Leow21-Nov-13 15:49
professionalPeter Leow21-Nov-13 15:49 
GeneralRe: Flaw in Table Design Pin
Vijay-Srivastava21-Nov-13 18:02
Vijay-Srivastava21-Nov-13 18:02 
QuestionSemicolon delimited lists Pin
.dan.g.21-Nov-13 12:50
professional.dan.g.21-Nov-13 12:50 
AnswerRe: Semicolon delimited lists Pin
Vijay-Srivastava21-Nov-13 18:04
Vijay-Srivastava21-Nov-13 18:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.