mysql - Joining table to union of two tables? -

i have 2 tables: orders , oldorders. both structured same way. want union these 2 tables , join them table: users. had orders , users, trying shoehorn oldorders current code.

select u.username, count(user) cnt     orders o         left join users u          on u.userident = o.user     shipped = 1     , total != 0     group user 

this finds number of nonzero total orders users have made in table orders, want in union of orders , oldorders. how can accomplish this?

create table orders (     user int,     shipped int,     total decimal(4,2) ); insert 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 users values     ("bob", 5); 

output is:

+----------+-----+ | username | cnt | +----------+-----+ |   bob    |  4  | +----------+-----+ 

after creating oldorders table:

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

the expected output when run on union of 2 tables is:

+----------+-----+ | username | cnt | +----------+-----+ |   bob    |  6  | +----------+-----+ 

just not sure or how shoehorn union there. instead of running query on orders, needs on orders union oldorders. can assumed there no intersect between 2 tables.

you need union way:

select u.username, count(user) cnt  (     select * orders      union     select * oldorders ) o left join users u on u.userident = o.user shipped = 1 , total != 0 group user; 

first combined orders using union between orders , oldorders table.

the rest of work same did.

---

see demo

---

note:

left join doesn't make sense in case. orders users don't exist null 0 output. doesn't hold value.

if want <user,total orders> users including users might not have ordered yet need change order of left join