self-join-sample.sql

-- 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/