在mySQL中减去两个查询结果

Question

I have 2 queries which gives all records(including the booked ones) and gives out booked records only. I wanted to subtract the two tables so that it only shows the unbooked records, here are the example of the query results:

Query 1:

+--------+--------+
| Number | AreaNo | 
+--------+--------+ 
|      6 |      A |  
|      6 |      B |  
|      6 |      C |  
|      7 |      A | 
|      7 |      B | 
+--------+--------+

Query 2:

+--------+--------+
| Number | AreaNo | 
+--------+--------+ 
|      6 |      B |  
|      6 |      C |   
|      7 |      B | 
+--------+--------+

Desired Results:

+--------+--------+
| Number | AreaNo | 
+--------+--------+ 
|      6 |      A |  
|      7 |      A | 
|      7 |      C | 
+--------+--------+

I know that I can't use MINUS in mySQL but I'm not sure that LEFT JOIN works in this situation. If this doesn't work, is it possible to work on the where clause?(Like if the number match, it only clear out the one with matching AreaNo). I tried this with two AND clause and it doesn't work. It clears out the results that doesnt fit either criteria. I have been doing researches over a week and nothing works. Please help, I am really desperated.

Query 1:

SELECT bookingListNo,
       areaNo 
  FROM BookingList, 
       BookingArea, 
       BookingLocation 
 WHERE bookingListNo NOT IN (SELECT bookingListNo 
                               FROM Booking 
                              WHERE bookingAreaNo IS NULL) AND 
       BookingList.bookingLocationNo = BookingLocation.bookingLocationNo AND
       BookingLocation.BookingLocationNo = BookingArea.bookingLocationNo 

Query 2:

SELECT bookingListNo, 
       areaNo 
  FROM Booking, 
       BookingArea 
 WHERE Booking.bookingAreaNo = BookingArea.bookingAreaNo

Answer 1

Check out this article for reference: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

I'm guessing a RIGHT JOIN would do the trick.

Not exactly sure what your tables are like, but if you are joining a table with unbooked data to one that has all of our items, then you could do a query like this:

SELECT *
FROM all_items
RIGHT JOIN unbooked_data ON all_items.item_id = unbooked_data.item_id

When you RIGHT JOIN it only selects the items that are in the table you are joining on that have matches in the table being joined to. This should allow you to select unbooked data. If this doesn't fit your situation, including your queries in your question might help us answer your problem more directly.