MYSQL query Design

chrisio

Hi Newbie here.
Im trying to design a query that will allow me to tell if I have a record in my db that matches.

The fields I have in my db are
cid(int)
resort(int)
villa(int)
startdate(date yyyy-mm-dd)
enddate(date yyyy-mm-dd)
slot (am/pm)

the only record i have in there at this moment in time is

cid=1
resort=1
villa=1
startdate=2006-08-06
enddate=2006-08-12
slot=am

In my query if I have a direct match on the following fields I return a result, 'villa','startdate','enddate' and 'slot'.
Now thats all well and good however I also need to take into account the fact the startdate could be later or the enddate could be earlier :-

Select * from bookings Where villa=1 AND startdate='2006-08-07' AND enddate='2006-08-11' AND slot=pm.

whats making this harder for me to understand is that I can have an overlap on the enddate as long as the slot is different. SO:-

Select * from bookings Where villa=1 AND startdate='2006-08-12' AND enddate='2006-08-16' AND slot=pm.

would still be valid even though the startdate is clashing as the slot is different.

I hope this makes sense and thanks
CHris

jheitz

Not sure I can make sense of it Chris... but I think what you need to do it use

WHERE ... startdate >='2006-08-06' AND enddate <= '2006-08-12'

cheerio

In my opinion, when you put dates in SQL, just use time(). It's much easier in the longrun. You can format it afterwards without changing everything in SQL, and you can even make relative dates like "1 Hour ago" or "1 Day Ago"

mourisj

Hello,

I think your "slot" problem has not been adressed so far .
I have 2 suggestions how to resolve your problem. The first one will work almost certainly, the second one might also work, although I am not 100% sure about the correct syntax, as I did no MySql for month, and I am used to some Oracle specific things.

1st suggestion: use a UNION:

Select from bookings Where villa=1 AND startdate>='2006-08-12' AND enddate<='2006-08-16' AND slot=pm.
UNION
Select from bookings Where villa=1 AND startdate>='2006-08-12' AND enddate<'2006-08-16'

If enddate < suggested date, then Ok; if enddate = suggested date AND slot = pm, then OK, otherwise not OK

2nd suggestion:

Select * from bookings Where villa=1 AND startdate>='2006-08-12'
AND ( (enddate<='2006-08-16' AND slot=pm) OR (enddate < '2006-08-16') )

Because of operator precedence, carefully use the brackets to enclose the conditions! If you omit the outer brackets , the OR will take precedence over all the ANDs and return any line having an enddate < '2006-08-16', which is certainly not what you expect.

Hope this helps.

JJ Mouris