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.
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
This is how you can select all the records that didn't use the booking. It's called an excluding left join or left exclude join. http://www.magecorner.com/mysql-joins-explained/
I'm not sure if this would be faster than WHERE NOT EXISTS, but considering that has a subquery, I think this is faster.
SELECT emb.*
FROM exp_menucard_booking emb
LEFT JOIN transactions t
ON (
t.Name = emb.Name AND
t.Restaurant = emb.Restaurant
)
WHERE t.Trans_ID IS NULL
SELECT * FROM Booking WHERE NAME NOT IN (SELECT NAME FROM Transaction );