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, 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, 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)

http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — pala_, Apr 28 '15 at 01:05
and if that doesn't answer your question, then you need to provide table schema, as well as sample data for both, as well as the expected output from that data. — pala_, Apr 28 '15 at 01:29
Yeah I saw that post, but couldn't get that method to work, or the method I posted above. — ComicDavid, Apr 28 '15 at 02:06
Then if you could provide your tables and some sample data, or set up an sqlfiddle — pala_, Apr 28 '15 at 02:18
I edited the post to include the tables of dummy data I'm working with. It's not formatted well, but I didn't know how to do make it look nice. — ComicDavid, Apr 28 '15 at 03:15
easiest way is to just copy/paste the results of a select, then format it as code. do we need your games table for this? for the above data - what would be your expected output? — pala_, Apr 28 '15 at 03:17
thank you - that helped. please see the answer and demo below — pala_, Apr 28 '15 at 03:31
@comicdavid could you please take a moment to review my answer below - and let me know if it needs any changes — pala_, Apr 28 '15 at 04:02

pala_ · Answer 1 · 2015-04-28T03:33:43.883

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

hrunting · Accepted Answer · 2015-04-28T23:38:01.813

You're on the right track with using a FULL OUTER JOIN, you just have the implementation incorrect.

A FULL OUTER JOIN in MySQL can be thought of as the UNION of a LEFT JOIN and a RIGHT JOIN. In your query, you're trying to approximate this by treating the RIGHT JOIN part of the logic as an inverse LEFT JOIN of the two tables, but your first part doesn't work because it's not a subselect with the same GROUP BY sequence as your first LEFT JOIN.

Simplest thing to do is simply take your first LEFT JOIN query stanza, copy it to the second stanza, and replace LEFT JOIN with RIGHT JOIN, then link the results to your games table, like so:

SELECT g.gameID, IFNULL(q.quantity, 0) AS quantity, g.gameName,
       IFNULL(q.payYes, 0) AS payYes, IFNULL(q.payNo, 0) AS payNo
  FROM games g
       INNER JOIN (
         SELECT IFNULL(rwl.gameID, res.gameID) AS gameID, rwl.quantity,
                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 IFNULL(rwl.gameID, res.gameID) AS gameID, rwl.quantity,
                res.payYes, res.payNo
           FROM RestockWishList rwl
                RIGHT 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
       ) AS q ON g.gameID = q.gameID

SQL Fiddle Results

i would go so far as to say a `full outer join` was actually being on the completely wrong track — pala_, Apr 28 '15 at 03:32
Ok, so this gets all the data I need (thank you). Is there a way to combine the gameIDs from both tables into one column, and then set all the NULLs to 0s like I did in the original query? This would help a lot with how I process it in the PHP and how it's displayed in the HTML. If I can maintain the original format rather than having to make changes all over the website, that'd be best. — ComicDavid, Apr 28 '15 at 03:39
To get it to match the output in your updated answer, link the `games` table against the `UNION` query via a subquery. Since the `games` table requires a consistent `gameID` for the linking, I modified the `UNION` query to optionally use the `gameID` from the `ReservationsBuy` if `RestockWishList.gameID` is not available. — hrunting, Apr 28 '15 at 23:36
Used coalesce on gameID and all the other fields and now it works the way I want. Thank you! — ComicDavid, Apr 28 '15 at 23:44
Yeah, old MySQL habits die hard. `COALESCE` is the proper way to handle `NULL`s. — hrunting, Apr 28 '15 at 23:49

score 0 · Answer 3 · answered Apr 28 '15 at 03:51

ok. So we now that always exists a record in game table, right?

Then start your FROM with this table, then you just have to do a LEFT JOIN for each table as follow:

SELECT
   rwl.*
 , g.gameName
 , coalesce(payYes, 0) payYes
 , coalesce(payNo, 0) payNo
FROM
  Games AS g LEFT JOIN
    RestockWishList AS rwl ON rwl.gameID = g.gameID 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
;

As you can say, the only changes were: Start the FROM with Games table and use LEFT JOIN, also remove the condition from WHERE and put it in the LEFT JOIN

MySQL outer join substitute

3 Answers3