circle-info
Live Webinar | From Strategy to Execution: Your Practical MySQL to MariaDB Migration Plan
Register Nowarrow-up-right
search
Download MariaDBContact Us

Advanced Joins

Explore complex join scenarios. This guide covers filtering joined data with WHERE clauses, handling dates, and aggregating results from multiple tables for deeper analysis.

This article is a continuation of the Joining Tables with JOIN Clauses Guide. If you're getting started with JOIN statements, review that page first.

hashtag
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:

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:

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:

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:

hashtag
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.

hashtag
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":

The result looks like this:

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.

hashtag
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:

The result looks like this:

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.

hashtag
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:

The result (limited to 10 rows) looks like this:

hashtag
See Also

The first version of this article was copied, with permission, from More_Advanced_Joinsarrow-up-right on 2012-10-05.

This page is licensed: CC BY-SA / Gnu FDL

spinner
PreviousJoining Tables with JOIN ClausesNextConfiguring MariaDB for Remote Client Access Guide

Last updated

Was this helpful?