MySQL external replacement

Question

I am working on a game inventory management system and would like to show the wish list of the replenishment owner and the calculation of customer purchase orders for each game in one table. I wrote a query that I thought worked, but then I noticed that it actually omits any games for which there are reservations, but which are not originally on the restock wish list. Request 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, Castle for all seasons, 0, 0

2, 2, A few acres of snow, 0, 0

18, 4, Alhambra, 0, 0

54, 2, Big Boggli, 2, 0

Apparently, the solution to this problem is to use FULL OUTER JOIN instead of LEFT JOIN, but MySQL does not support this function. I spent hours trying to translate it into a UNION structure, but can't make it work correctly. It is as close as mine:

 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 be displayed, as I originally wrote it, just with 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 Value

14

2, 2

18, 4

54, 2

ReservationsBuy:

GameID, customerEmail, Customer Name, dateReserved, Purchase Date, dateClaimed, prepayment

30, [e-mail] , Diana, 2015-04-24 14:46:05, ZERO, ZERO, Yes

54, [e-mail] , Cart, 2015-04-24 14:43:32, ZERO, ZERO, Yes

54, [e-mail] , Manny, 2015-04-27 19:48:22, ZERO, ZERO, Yes

43, [e-mail] , grandmother, 2015-04-23 22:32:03, ZERO, ZERO, No

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

1, 4, Castle for all seasons, 0, 0

2, 2, A 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

(The game table is not particularly important for this query. Only the relevance is that both ReservationsBuy and RestockWishList are associated with the Game game).

Answer 1

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

A FULL OUTER JOIN in MySQL can be thought of as a UNION for a LEFT JOIN and a RIGHT JOIN . In your query, you are trying to approximate this by treating the RIGHT JOIN part of the logic as the inverse LEFT JOIN for two tables, but your first part does not work because it is not a subquery with the same GROUP BY as your first LEFT JOIN .

The easiest way to do this is simply take your first LEFT JOIN query line, copy it to the second stanza and replace LEFT JOIN with RIGHT JOIN , and then combine the results with your game table, for example:

 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

Answer 2

I think maybe you need a query like this - not a complete 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 is a demonstration here , but the bottom line is that for each game in the game table it will give you the total reservation, the number from the wish list, and we use a conditional number to indicate the counter prepaid = yes or prepaid = no . Effectively, it is simply combining two small requests for a common gameplay.

If you want this to include filtering by date, etc., you may need to learn more about how you want the results to work or display

Answer 3

OK. So now we always have an entry in the game table, right?

Then run FROM using this table, then you just need to do a LEFT JOIN for each table as follows:

 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 tell, the only changes were: Run FROM with the Games table and use the LEFT JOIN , also remove the condition from WHERE and put it in the LEFT JOIN