# Advanced Joins This article is a continuation of the [Joining Tables with JOIN Clauses Guide](/docs/server/mariadb-quickstart-guides/mariadb-join-guide.md). If you're getting started with `JOIN` statements, review that page first. ## The Employee Database Let us begin by using an example employee database of a fairly small family business, which does not anticipate expanding in the future. First, we create the table that will hold all of the employees and their contact information: ```sql CREATE TABLE `Employees` ( `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, `First_Name` VARCHAR(25) NOT NULL, `Last_Name` VARCHAR(25) NOT NULL, `Position` VARCHAR(25) NOT NULL, `Home_Address` VARCHAR(50) NOT NULL, `Home_Phone` VARCHAR(12) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM; ``` Next, we add a few employees to the table: ```sql INSERT INTO `Employees` (`First_Name`, `Last_Name`, `Position`, `Home_Address`, `Home_Phone`) VALUES ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492'), ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847'), ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456'), ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349'), ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329'), ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478'); ``` Now, we create a second table, containing the hours which each employee clocked in and out during the week: ```sql CREATE TABLE `Hours` ( `ID` TINYINT(3) UNSIGNED NOT NULL, `Clock_In` DATETIME NOT NULL, `Clock_Out` DATETIME NOT NULL ) ENGINE=MyISAM; ``` Finally, although it is a lot of information, we add a full week of hours for each of the employees into the second table that we created: ```sql INSERT INTO `Hours` VALUES ('1', '2005-08-08 07:00:42', '2005-08-08 17:01:36'), ('1', '2005-08-09 07:01:34', '2005-08-09 17:10:11'), ('1', '2005-08-10 06:59:56', '2005-08-10 17:09:29'), ('1', '2005-08-11 07:00:17', '2005-08-11 17:00:47'), ('1', '2005-08-12 07:02:29', '2005-08-12 16:59:12'), ('2', '2005-08-08 07:00:25', '2005-08-08 17:03:13'), ('2', '2005-08-09 07:00:57', '2005-08-09 17:05:09'), ('2', '2005-08-10 06:58:43', '2005-08-10 16:58:24'), ('2', '2005-08-11 07:01:58', '2005-08-11 17:00:45'), ('2', '2005-08-12 07:02:12', '2005-08-12 16:58:57'), ('3', '2005-08-08 07:00:12', '2005-08-08 17:01:32'), ('3', '2005-08-09 07:01:10', '2005-08-09 17:00:26'), ('3', '2005-08-10 06:59:53', '2005-08-10 17:02:53'), ('3', '2005-08-11 07:01:15', '2005-08-11 17:04:23'), ('3', '2005-08-12 07:00:51', '2005-08-12 16:57:52'), ('4', '2005-08-08 06:54:37', '2005-08-08 17:01:23'), ('4', '2005-08-09 06:58:23', '2005-08-09 17:00:54'), ('4', '2005-08-10 06:59:14', '2005-08-10 17:00:12'), ('4', '2005-08-11 07:00:49', '2005-08-11 17:00:34'), ('4', '2005-08-12 07:01:09', '2005-08-12 16:58:29'), ('5', '2005-08-08 07:00:04', '2005-08-08 17:01:43'), ('5', '2005-08-09 07:02:12', '2005-08-09 17:02:13'), ('5', '2005-08-10 06:59:39', '2005-08-10 17:03:37'), ('5', '2005-08-11 07:01:26', '2005-08-11 17:00:03'), ('5', '2005-08-12 07:02:15', '2005-08-12 16:59:02'), ('6', '2005-08-08 07:00:12', '2005-08-08 17:01:02'), ('6', '2005-08-09 07:03:44', '2005-08-09 17:00:00'), ('6', '2005-08-10 06:54:19', '2005-08-10 17:03:31'), ('6', '2005-08-11 07:00:05', '2005-08-11 17:02:57'), ('6', '2005-08-12 07:02:07', '2005-08-12 16:58:23'); ``` ## Working with the Employee Database Now that we have a cleanly structured database to work with, let us begin this tutorial by stepping up one notch from the last tutorial and filtering our information a little. ### Filtering by Name Earlier in the week, an anonymous employee reported that Helmholtz came into work almost four minutes late; to verify this, we will begin our investigation by filtering out employees whose first names are "Helmholtz": ```sql SELECT `Employees`.`First_Name`, `Employees`.`Last_Name`, `Hours`.`Clock_In`, `Hours`.`Clock_Out` FROM `Employees` INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID` WHERE `Employees`.`First_Name` = 'Helmholtz'; ``` The result looks like this: ``` +------------+-----------+---------------------+---------------------+ | First_Name | Last_Name | Clock_In | Clock_Out | +------------+-----------+---------------------+---------------------+ | Helmholtz | Watson | 2005-08-08 07:00:12 | 2005-08-08 17:01:02 | | Helmholtz | Watson | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 | | Helmholtz | Watson | 2005-08-10 06:54:19 | 2005-08-10 17:03:31 | | Helmholtz | Watson | 2005-08-11 07:00:05 | 2005-08-11 17:02:57 | | Helmholtz | Watson | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 | +------------+-----------+---------------------+---------------------+ 5 rows in set (0.00 sec) ``` This is obviously more information than we care to trudge through, considering we only care about when he arrived past 7:00:59 on any given day within this week; thus, we need to add a couple more conditions to our `WHERE` clause. ### Filtering by Name, Date and Time In the following example, we will filter out all of the times which Helmholtz clocked in that were before 7:01:00 and during the work week that lasted from the 8th to the 12th of August: ```sql SELECT `Employees`.`First_Name`, `Employees`.`Last_Name`, `Hours`.`Clock_In`, `Hours`.`Clock_Out` FROM `Employees` INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID` WHERE `Employees`.`First_Name` = 'Helmholtz' AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08' AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12' AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59'; ``` The result looks like this: ``` +------------+-----------+---------------------+---------------------+ | First_Name | Last_Name | Clock_In | Clock_Out | +------------+-----------+---------------------+---------------------+ | Helmholtz | Watson | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 | | Helmholtz | Watson | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 | +------------+-----------+---------------------+---------------------+ 2 rows in set (0.00 sec) ``` By merely adding a few more conditions, we eliminated all of the irrelevant information; Helmholtz was late to work on the 9th and the 12th of August. ### Displaying Total Work Hours per Day Suppose you would like to—based on the information stored in both of our tables in the employee database—develop a quick list of the total hours each employee has worked for each day recorded; a simple way to estimate the time each employee worked per day is exemplified below: ```sql SELECT `Employees`.`ID`, `Employees`.`First_Name`, `Employees`.`Last_Name`, `Hours`.`Clock_In`, `Hours`.`Clock_Out`, DATE_FORMAT(`Hours`.`Clock_Out`, '%T')-DATE_FORMAT(`Hours`.`Clock_In`, '%T') AS 'Total_Hours' FROM `Employees` INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`; ``` The result (limited to 10 rows) looks like this: ``` +----+------------+-----------+---------------------+---------------------+-------------+ | ID | First_Name | Last_Name | Clock_In | Clock_Out | Total_Hours | +----+------------+-----------+---------------------+---------------------+-------------+ | 1 | Mustapha | Mond | 2005-08-08 07:00:42 | 2005-08-08 17:01:36 | 10 | | 1 | Mustapha | Mond | 2005-08-09 07:01:34 | 2005-08-09 17:10:11 | 10 | | 1 | Mustapha | Mond | 2005-08-10 06:59:56 | 2005-08-10 17:09:29 | 11 | | 1 | Mustapha | Mond | 2005-08-11 07:00:17 | 2005-08-11 17:00:47 | 10 | | 1 | Mustapha | Mond | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 | 9 | | 2 | Henry | Foster | 2005-08-08 07:00:25 | 2005-08-08 17:03:13 | 10 | | 2 | Henry | Foster | 2005-08-09 07:00:57 | 2005-08-09 17:05:09 | 10 | | 2 | Henry | Foster | 2005-08-10 06:58:43 | 2005-08-10 16:58:24 | 10 | | 2 | Henry | Foster | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 | 10 | | 2 | Henry | Foster | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 | 9 | +----+------------+-----------+---------------------+---------------------+-------------+ 10 rows in set (0.00 sec) ``` ## See Also * [Joining Tables with JOIN Clauses Guide](/docs/server/mariadb-quickstart-guides/mariadb-join-guide.md) * [JOIN Syntax](/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins/join-syntax.md) * [Comma vs JOIN](/docs/server/reference/sql-statements/data-manipulation/selecting-data/joins/comma-vs-join.md) * [Joins, Subqueries and SET](/docs/server/reference/sql-structure/joins-subqueries-set.md) *The first version of this article was copied, with permission, from* [*More\_Advanced\_Joins*](https://hashmysql.org/wiki/More_Advanced_Joins) *on 2012-10-05.* *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/mariadb-quickstart-guides/more-advanced-joins.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.