Last active
February 3, 2016 00:18
-
-
Save element121/0984ce881c0cb043c300 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This is MySQL sample provided from http://element121.com/2016/02/02/how-to-join-the-same-table-three-times-in-a-single-sql-query-statement/ | |
-- phpMyAdmin SQL Dump | |
-- version 4.1.14 | |
-- http://www.phpmyadmin.net | |
-- | |
-- Host: 127.0.0.1 | |
-- Generation Time: Jan 11, 2016 at 10:57 PM | |
-- Server version: 5.6.17 | |
-- PHP Version: 5.5.12 | |
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; | |
SET time_zone = "+00:00"; | |
-- | |
-- Database: `test` | |
-- | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `products` | |
-- | |
DROP TABLE IF EXISTS `products`; | |
CREATE TABLE IF NOT EXISTS `products` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`created_by_user_id` int(11) NOT NULL, | |
`modified_by_user_id` int(11) DEFAULT NULL, | |
`approved_by_user_id` int(11) DEFAULT NULL, | |
PRIMARY KEY (`id`), | |
KEY `created_by_user_id` (`created_by_user_id`,`modified_by_user_id`,`approved_by_user_id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; | |
-- | |
-- Dumping data for table `products` | |
-- | |
INSERT INTO `products` (`id`, `created_by_user_id`, `modified_by_user_id`, `approved_by_user_id`) VALUES | |
(1, 1, NULL, NULL), | |
(2, 1, 1, 1), | |
(3, 2, NULL, NULL), | |
(4, 2, 1, 2); | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `users` | |
-- | |
DROP TABLE IF EXISTS `users`; | |
CREATE TABLE IF NOT EXISTS `users` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`first_name` varchar(50) NOT NULL, | |
`last_name` varchar(50) NOT NULL, | |
PRIMARY KEY (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; | |
-- | |
-- Dumping data for table `users` | |
-- | |
INSERT INTO `users` (`id`, `first_name`, `last_name`) VALUES | |
(1, 'Jon', 'Crump'), | |
(2, 'Keith', 'Brown'), | |
(3, 'Kelly', 'Smith'), | |
(4, 'Bob', 'Green'); | |
-- If you want to find out who created the product, you can do that with a simple SQL query like this: | |
SELECT | |
p.id | |
, u.firstname AS 'creator_firstname' | |
, u.lastname AS 'creator_lastname' | |
FROM products p | |
INNER JOIN users u | |
ON p.created_by_user_id = u.id | |
-- If you want to find out who modified the product you can do that with a similar query: | |
SELECT | |
p.id | |
, u.firstname AS 'modifier_firstname' | |
, u.lastname AS 'modifier_lastname' | |
FROM products p | |
INNER JOIN users u | |
ON p.modified_by_user_id = u.id | |
-- If you want to find out who approved the product you can do that with a similar query: | |
SELECT | |
p.id | |
, u.firstname AS 'approver_firstname' | |
, u.lastname AS 'approver_lastname' | |
FROM products p | |
INNER JOIN users u | |
ON p.approved_by_user_id = u.id | |
-- But how can you combine all three queries to know the user that created and modified and approved the products in one query? | |
-- Just join the Users table three times, but you need to use a different alias each time you use the same table: | |
SELECT | |
p.id | |
, u1.firstname AS 'creator_firstname' | |
, u1.lastname AS 'creator_lastname' | |
, u2.firstname AS 'modifier_firstname' | |
, u2.lastname AS 'modifier_lastname' | |
, u3.firstname AS 'approver_firstname' | |
, u3.lastname AS 'approver_lastname' | |
FROM products p | |
INNER JOIN users u1 | |
ON p.created_by_user_id = u1.id | |
INNER JOIN users u2 | |
ON p.modified_by_user_id = u2.id | |
INNER JOIN users u3 | |
ON p.approved_by_user_id = u3.id | |
-- But what if a product has only been created and modified, but hasn’t yet been modified? In which case the approved_by_user_id should be null in your products table. Then the above query won’t be able to join three times to the users table. Unless it has a user record with a null id, which it shouldn’t. In this case you need to use LEFT JOIN like this: | |
SELECT | |
p.id | |
, u1.firstname AS 'creator_firstname' | |
, u1.lastname AS 'creator_lastname' | |
, u2.firstname AS 'modifier_firstname' | |
, u2.lastname AS 'modifier_lastname' | |
, u3.firstname AS 'approver_firstname' | |
, u3.lastname AS 'approver_lastname' | |
FROM products p | |
INNER JOIN users u1 | |
ON p.created_by_user_id = u1.id | |
INNER JOIN users u2 | |
ON p.modified_by_user_id = u2.id | |
LEFT JOIN users u3 | |
ON p.approved_by_user_id = u3.id | |
-- This is MySQL sample provided from http://element121.com/2016/02/02/how-to-join-the-same-table-three-times-in-a-single-sql-query-statement/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment