gator gator - 28 days ago 5
MySQL Question

Joining table to union of two tables?

I have two tables:

orders
and
oldorders
. Both are structured the same way. I want to union these two tables and then join them to another table:
users
. Previously I only had
orders
and
users
, I am trying to shoehorn
oldorders
into my current code.

SELECT u.username, COUNT(user) AS cnt
FROM orders o
LEFT JOIN users u
ON u.userident = o.user
WHERE shipped = 1
AND total != 0
GROUP BY user


This finds the number of nonzero total orders all users have made in table
orders
, but I want to this in the union of
orders
and
oldorders
. How can I accomplish this?

create table orders (
user int,
shipped int,
total decimal(4,2)
);
insert into orders values
(5, 1, 28.21),
(5, 1, 24.12),
(5, 1, 19.99),
(5, 1, 59.22);
create table users (
username varchar(100),
userident int
);
insert into users values
("Bob", 5);


Output for this is:

+----------+-----+
| username | cnt |
+----------+-----+
| Bob | 4 |
+----------+-----+


After creating the
oldorders
table:

create table oldorders (
user int,
shipped int,
total decimal(4,2)
);
insert into oldorders values
(5, 1, 62.94),
(5, 1, 53.21);


The expected output when run on the union of the two tables is:

+----------+-----+
| username | cnt |
+----------+-----+
| Bob | 6 |
+----------+-----+


Just not sure where or how to shoehorn a union into there. Instead of running the query on
orders
, it needs to be on
orders union oldorders
. It can be assumed there is no intersect between the two tables.

Answer

You just need to union this way:

SELECT u.username, COUNT(user) AS cnt
FROM 
(
    SELECT * FROM orders 
    UNION
    SELECT * FROM oldorders
) o
LEFT JOIN users u ON u.userident = o.user
WHERE shipped = 1
AND total != 0
GROUP BY user;

First get the combined orders using UNION between orders and oldorders table.

The rest of the work is exactly same what you did.


SEE DEMO


Note:

Left join doesn't make sense in this case. Orders for which the users don't exist then you will get NULL NULL as output. This doesn't hold any value.

If you want <user,total orders> for all users including users who might not have ordered yet then you need to change the order of the LEFT JOIN

Comments