Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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
You can’t perform that action at this time.