i working on game inventory management system , display owner's restock wish list , count of customer buy reservations each game in single table. wrote query thought working, noticed omits games there reservations aren't in restock wish list. query below:
select rwl.*, g.gamename, coalesce(payyes, 0) payyes, coalesce(payno, 0) payno restockwishlist rwl, games g left join (select gameid, count(if(prepaid='yes', 1, null)) payyes, count(if(prepaid='no', 1, null)) payno reservationsbuy group gameid) res on res.gameid = g.gameid rwl.gameid = g.gameid; query results: gameid, quantity, gamename, payyes, payno
1, 4, castle seasons, 0, 0
2, 2, few acres of snow, 0, 0
18, 4, alhambra, 0, 0
54, 2, big boggle, 2, 0
apparently solution problem use full outer join instead of left join, mysql doesn't support function. have spent hours trying translate union structure, can't quite work correctly. close i've got:
select rwl.*, res.gameid, res.payyes, res.payno restockwishlist rwl left join (select gameid, count(if(prepaid='yes', 1, null)) payyes, count(if(prepaid='no', 1, null)) payno reservationsbuy group gameid) res on res.gameid = rwl.gameid union select rwl.*, res.gameid, count(if(prepaid='yes', 1, null)) payyes, count(if(prepaid='no', 1, null)) payno reservationsbuy res left join restockwishlist rwl on rwl.gameid = res.gameid; query results: gameid, quantity, gameid, payyes, payno
1, 4, null, null, null
2, 2, null, null, null
18, 4, null, null, null
54, 2, 54, 2, 0
null, null, 30, 3, 1
(sorry, don't know how nicely format query table results in stackoverflow.)
i want query display wrote it, missing values reservationsbuy. specific please?
tables:
create table if not exists restockwishlist ( gameid int(6), quantity int(3) not null, primary key (gameid), foreign key (gameid) references games(gameid) on update cascade on delete cascade); create table if not exists reservationsbuy ( gameid int(6), customeremail varchar(25) not null, customername varchar(25) not null, datereserved datetime not null, #date customer files game reservation datepurchased datetime, #date board , brew restocks game dateclaimed datetime, #date customer physically claims game prepaid enum('yes', 'no') not null, primary key (gameid, customeremail), foreign key (gameid) references games (gameid) on update cascade on delete cascade); sample data: restockwishlist:
gameid, quantity
1, 4
2, 2
18, 4
54, 2
reservationsbuy:
gameid, customeremail, customername, datereserved, datepurchased, dateclaimed, prepaid
30, wonder@woman.com, diana, 2015-04-24 14:46:05, null, null, yes
54, boggie@marsh.com, boggie, 2015-04-24 14:43:32, null, null, yes
54, manny@second.com, manny, 2015-04-27 19:48:22, null, null, yes
43, old@mom.com, grandma, 2015-04-23 22:32:03, null, null, no
expected output: gameid, quantity, gamename, payyes, payno
1, 4, castle seasons, 0, 0
2, 2, few acres of snow, 0, 0
18, 4, alhambra, 0, 0
30, 0, arkham horror, 1, 0
43, 0, bananagrams, 0, 1
54, 2, big boggle, 2, 0
(games table not particularly important query. relevance both reservationsbuy , restockwishlist connected games gameid)
you're on right track using full outer join, have implementation incorrect.
a full outer join in mysql can thought of union of left join , right join. in query, you're trying approximate treating right join part of logic inverse left join of 2 tables, first part doesn't work because it's not subselect same group by sequence first left join.
simplest thing take first left join query stanza, copy second stanza, , replace left join right join, link results games table, so:
select g.gameid, ifnull(q.quantity, 0) quantity, g.gamename, ifnull(q.payyes, 0) payyes, ifnull(q.payno, 0) payno games g inner join ( select ifnull(rwl.gameid, res.gameid) gameid, rwl.quantity, res.payyes, res.payno restockwishlist rwl left join ( select gameid, count(if(prepaid='yes', 1, null)) payyes, count(if(prepaid='no', 1, null)) payno reservationsbuy group gameid ) res on res.gameid = rwl.gameid union select ifnull(rwl.gameid, res.gameid) gameid, rwl.quantity, res.payyes, res.payno restockwishlist rwl right join ( select gameid, count(if(prepaid='yes', 1, null)) payyes, count(if(prepaid='no', 1, null)) payno reservationsbuy group gameid ) res on res.gameid = rwl.gameid ) q on g.gameid = q.gameid 