title: MySQL outer join substitute
body: <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Query results:
  gameID,
  quantity,
  gameName,
  payYes,
  payNo</p>
  
  <p>1,
  4,
  A Castle for all Seasons,
  0,
  0</p>
  
  <p>2,
  2,
  A Few Acres of Snow,
  0,
  0</p>
  
  <p>18,
  4,
  Alhambra,
  0,
  0</p>
  
  <p>54,
  2,
  Big Boggle,
  2,
  0</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Query results:
  gameID, quantity, gameID, payYes, payNo</p>
  
  <p>1, 4, NULL, NULL, NULL</p>
  
  <p>2, 2, NULL, NULL, NULL</p>
  
  <p>18, 4, NULL, NULL, NULL</p>
  
  <p>54, 2, 54, 2, 0</p>
  
  <p>NULL, NULL, 30, 3, 1</p>
  
  <p>(Sorry, I don't know how to nicely format query table results in StackOverflow.)</p>
  
  <p>I want the query to display as I originally wrote it, just with the missing values from ReservationsBuy. Specific help please?</p>
  
  <p>Tables:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Sample data:
  RestockWishList:</p>
  
  <p>gameID,
  quantity</p>
  
  <p>1,
  4</p>
  
  <p>2,
  2</p>
  
  <p>18,
  4</p>
  
  <p>54,
  2</p>
  
  <p>ReservationsBuy:</p>
  
  <p>gameID,
  customerEmail,
  customerName,
  dateReserved,
  datePurchased,
  dateClaimed,
  prepaid</p>
  
  <p>30,
  wonder@woman.com,
  Diana,
  2015-04-24 14:46:05,
  NULL,
  NULL,
  Yes</p>
  
  <p>54,
  boggie@marsh.com,
  boggie,
  2015-04-24 14:43:32,
  NULL,
  NULL,
  Yes</p>
  
  <p>54,
  manny@second.com,
  manny,
  2015-04-27 19:48:22,
  NULL,
  NULL,
  Yes</p>
  
  <p>43,
  old@mom.com,
  grandma,
  2015-04-23 22:32:03,
  NULL,
  NULL,
  No</p>
  
  <p>Expected output:
  gameID, quantity, gameName, payYes, payNo</p>
  
  <p>1, 4, A Castle for all Seasons, 0, 0</p>
  
  <p>2, 2, A Few Acres of Snow, 0, 0</p>
  
  <p>18, 4, Alhambra, 0, 0</p>
  
  <p>30, 0, Arkham Horror, 1, 0</p>
  
  <p>43, 0, Bananagrams, 0, 1</p>
  
  <p>54, 2, Big Boggle, 2, 0</p>
  
  <p>(Games table not particularly important for this query. Only relevance is that both ReservationsBuy and RestockWishList are connected to Games by gameID)</p>