如何比较表格并获得差异

Question

I have some problems to compare two of my tables. Usually I show my attempt how to solve a problem but here I do not know what to do. Normally, I do it in php. So i get all the information from 2 tables and then compare. But I would like to do it in MySQL. I hope you can help.

The first table, is my transactions table. This is the place where people have used their cards in restaurants.

The second table is my booking table. This is the place where people book reservations in restaurant.

I want to compare these tables, so i can get those who has NOT used their reservation

Transaction mySQL

SELECT t.*, em.* FROM transactions as t
  left join exp_members as em on (t.cardid-10000000 = em.member_id) 
  left JOIN exp_member_data emd on em.member_id = emd.member_id ORDER BY t.created DESC

Transaction Table.

Trans_ID     TransactionTime         Name            Restaurant 
1852         2013-04-08 12:45:21     Christian       La Canton
1851         2013-04-08 12:41:00     Zaz             Parken

Booking mySQL

SELECT b.* from exp_menucard_booking as b;

Booking Table:

ID      BookingTime          Name            NumberOfPeople  Restaurant 
270     2013-04-09 14:45:00  Christian       2               La Canton
269     2013-04-08 12:17:00  Toby            4               La Raz

As you can se, Toby from Booking table has not used his card (transaction tabel). How can i get him out of my tabel.

Answer 1

If you want to select all persons, who have already booked (there is an entry in booking table) but do not have an entry in the Transaction Table you could try this query:

SELECT * 
FROM `Booking` B 
WHERE NOT EXISTS (
    SELECT * 
    FROM `Transaction` T 
    WHERE T.name = B.name
); 

BTW it would be better, if you used some User_ID instead of name to identify the persons