-- This is MySQL sample provided from http://element121.com/2015/11/07/how-to-join-the-same-table-twice-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, | |
PRIMARY KEY (`id`), | |
KEY `created_by_user_id` (`created_by_user_id`,`modified_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`) VALUES | |
(1, 1, NULL), | |
(2, 1, 1), | |
(3, 2, NULL), | |
(4, 2, 1); | |
-- -------------------------------------------------------- | |
-- | |
-- 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 | |
-- But how can you combine both queries to know the user that created and modified the products in one query? | |
-- Just join the Users table twice, 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' | |
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 | |
-- But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table. Then the above query won’t be able to join twice 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' | |
FROM products p | |
INNER JOIN users u1 | |
ON p.created_by_user_id = u1.id | |
LEFT JOIN users u2 | |
ON p.modified_by_user_id = u2.id | |
-- This is MySQL sample provided from http://element121.com/2015/11/07/how-to-join-the-same-table-twice-in-a-single-sql-query-statement/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment