Jump to content

A little help with join query


mastubbs

Recommended Posts

Hi guys,

 

I’m really hoping someone can help with this query. I'm sure it must use join somehow but i cant work out exactly how to do it.

 

I have two tables (‘booking_slots’ and ‘booking_reservation’). ‘booking_slots’ has ‘slot_date’ and ‘slot_id’ fields. ‘booking_reservation’ has a number of fields including ‘slot_id’ but not ‘slot_date’.

 

I want to run a query to delete all records between a certain date range in BOTH tables (say for example 01 Jan 2012 to 01 Jan 2013). To do this I want to find and delete all records using ‘slot_date’ in ‘booking_slots’ and use the corresponding ‘slot_id’ of the deleted records to delete the records with the same ‘slot_id’ in ‘booking_reservation’.

 

Any help with this would be very greatly appreciated.

 

Thanks

Link to comment
Share on other sites

Not tested. Backup data before trying.

 

Assumes the dates are stored correctly in yyyy-mm-dd format (other formats will not work for range comparisons)

DELETE booking_slots, booking_reservation
FROM booking_slots
    INNER JOIN booking_reservation USING (slot_id)
WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'
Link to comment
Share on other sites

 

Not tested. Backup data before trying.

 

Assumes the dates are stored correctly in yyyy-mm-dd format (other formats will not work for range comparisons)

DELETE booking_slots, booking_reservation
FROM booking_slots
    INNER JOIN booking_reservation USING (slot_id)
WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'

 

 

Thanks very much for the help. Unfortunately it didn't work and i cant really figure out why. The dates are in the correct format but it only deleted 6 rows but there are thousands that should match. The only thing i can think to add is that although slot_id is sequential in booking_slots, it is not in booking_reservation. Ie, in booking_slots, booking_id numbers are 1,2,3,4 etc but in booking_reservation there may not be a 'reservation' with booking_id=2 (for example).  In context, there are hundreds of booking slots for each day, but they are not always filled. Only filled (reserved) slots end up in booking_reservation with the slot_id number for that slot - if that makes sense? I'm not sure if that would cause a problem with the query though?

Edited by mastubbs
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.