MySQL outer join substitute

Question

I am working on a game inventory management system and would like to display the owner's restock wish list and a count of customer buy reservations for each game in a single table. I wrote a query that I thought was working, but then I noticed that it actually omits any games for which there are reservations but that aren't initially in the restock wish list. The query is below:

SELECT rwl.*, g.gameName, coalesce(payYes, 0) payYes, coalesce(payNo, 0) payNo FROM RestockWishList AS rwl, Games AS g
        LEFT JOIN
        (SELECT gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes, COUNT(if(prepaid='No', 1, NULL)) payNo FROM ReservationsBuy GROUP BY gameID) AS res
        ON res.gameID = g.gameID
        WHERE rwl.gameID = g.gameID;

Query results: gameID, quantity, gameName, payYes, payNo

1, 4, A Castle for all Seasons, 0, 0

2, 2, A Few Acres of Snow, 0, 0

18, 4, Alhambra, 0, 0

54, 2, Big Boggle, 2, 0

Apparently the solution to this problem is to use FULL OUTER JOIN instead of LEFT JOIN, but MySQL doesn't support that function. I have spent hours trying to translate it to a UNION structure, but can't quite get it to work correctly. This is as close as I've got:

SELECT rwl.*, res.gameID, res.payYes, res.payNo FROM RestockWishList rwl
LEFT JOIN
(SELECT gameID, COUNT(if(prepaid='Yes', 1, NULL)) payYes, COUNT(if(prepaid='No', 1, NULL)) payNo FROM ReservationsBuy GROUP BY gameID) AS 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 FROM 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, I don't know how to nicely format query table results in StackOverflow.)

I want the query to display as I originally wrote it, just with the missing values from ReservationsBuy. Specific help 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 and 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, [email protected], Diana, 2015-04-24 14:46:05, NULL, NULL, Yes

54, [email protected], boggie, 2015-04-24 14:43:32, NULL, NULL, Yes

54, [email protected], manny, 2015-04-27 19:48:22, NULL, NULL, Yes

43, [email protected], grandma, 2015-04-23 22:32:03, NULL, NULL, No

Expected output: gameID, quantity, gameName, payYes, payNo

1, 4, A Castle for all Seasons, 0, 0

2, 2, A 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 for this query. Only relevance is that both ReservationsBuy and RestockWishList are connected to Games by gameID)

Answer 1

I think maybe you want a query like this - not a full outer join:

select q.id, q.name, q.reservations, ifnull(q2.wishcount, 0) wishcount, q.payYes, q.payNo
  from (
    select g.*, count(rb.gameid) reservations, count(case when prepaid = 'Yes' then 1 end) payYes, count(case when prepaid = 'No' then 1 end) payNo
      from games g
        left join reservationsbuy rb
          on g.id = rb.gameid
      group by g.id
  ) q
  left join (
    select g.id, sum(quantity) wishcount
      from games g
        left join restockwishlist rwl
          on g.id = rwl.gameid
      group by g.id
  ) q2
  on q.id = q2.id;

There's a demo here, but the gist of it is, for each game in the game table, it will give you the total number of reservations, the quantity from the wish list, and we use a conditional count to provide the count ofprepaid = yes, or prepaid = no. Effectively it is just joining together two small queries on the shared gameid.

If you want this to include filtering by date etc, you might need to be a bit more explicit about how you want the results to work, or display