Mysql LEFT JOINing same table twice

join;MySQL

mysql> SELECT * FROM customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+

mysql> SELECT * FROM purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+

So i have two tables and want to see a column with a customer name then a column with total number of dogs purchased followed by a column for cats and this is what i came up with

mysql> SELECT c.name, sum(dogs.quant) AS dogs, SUM(cats.quant) AS cats
    -> FROM customers AS c
    -> LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog'
    -> LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat'
    -> GROUP BY c.name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John | NULL |    5 |
| Matt |    3 |    8 |
+------+------+------+

Can someone explain why Matt has 8 cats when i expect it to be 4? This is a boiled down version of my problem

Best Answer

Instead of doing any LEFT JOIN, try this query

PROPOSED QUERY

SELECT
   IFNULL(name,'Total') name,
   SUM(IF(type='dog',quant,0)) dogs,
   SUM(IF(type='cat',quant,0)) cats
FROM
(
    SELECT AA.name,BB.type,SUM(BB.quant) quant
    FROM customers AA INNER JOIN purchases BB
    ON AA.id = BB.owner_id
    GROUP BY AA.name,BB.type
) A
GROUP BY name WITH ROLLUP;

SAMPLE DATA

drop database if exists meccooll;
create database meccooll;
use meccooll
create table customers
(
    id int not null,
    name varchar(20),
    primary key (id)
);
create table purchases
(
    owner_id int not null,
    type varchar(20),
    quant int not null,
    key (owner_id,type)
); 
insert into customers (id,name) values (1,'Matt'),(3,'John');
insert into purchases (owner_id,type,quant) values
(3,'cat',3),(3,'cat',2),(1,'cat',4),(1,'dog',1),(1,'dog',2);
select * from customers;
select * from purchases;

When you execute it

mysql> drop database if exists meccooll;
Query OK, 2 rows affected (0.28 sec)

mysql> create database meccooll;
Query OK, 1 row affected (0.00 sec)

mysql> use meccooll
Database changed
mysql> create table customers
    -> (
    ->     id int not null,
    ->     name varchar(20),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.47 sec)

mysql> create table purchases
    -> (
    ->     owner_id int not null,
    ->     type varchar(20),
    ->     quant int not null,
    ->     key (owner_id,type)
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> insert into customers (id,name) values (1,'Matt'),(3,'John');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into purchases (owner_id,type,quant) values
    -> (3,'cat',3),(3,'cat',2),(1,'cat',4),(1,'dog',1),(1,'dog',2);
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

you get this

mysql> select * from customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->    IFNULL(name,'Total') name,
    ->    SUM(IF(type='dog',quant,0)) dogs,
    ->    SUM(IF(type='cat',quant,0)) cats
    -> FROM
    -> (
    ->     SELECT AA.name,BB.type,SUM(BB.quant) quant
    ->     FROM customers AA INNER JOIN purchases BB
    ->     ON AA.id = BB.owner_id
    ->     GROUP BY AA.name,BB.type
    -> ) A
    -> GROUP BY name WITH ROLLUP;
+-------+------+------+
| name  | dogs | cats |
+-------+------+------+
| John  |    0 |    5 |
| Matt  |    3 |    4 |
| Total |    3 |    9 |
+-------+------+------+
3 rows in set, 1 warning (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED WITHOUT ROLLUP

mysql> SELECT
    ->    IFNULL(name,'Total') name,
    ->    SUM(IF(type='dog',quant,0)) dogs,
    ->    SUM(IF(type='cat',quant,0)) cats
    -> FROM
    -> (
    ->     SELECT AA.name,BB.type,SUM(BB.quant) quant
    ->     FROM customers AA INNER JOIN purchases BB
    ->     ON AA.id = BB.owner_id
    ->     GROUP BY AA.name,BB.type
    -> ) A
    -> GROUP BY name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John |    0 |    5 |
| Matt |    3 |    4 |
+------+------+------+
2 rows in set, 1 warning (0.00 sec)

mysql>

GIVE IT A TRY !!!

Related Question