sql date prob.. 1

[burnside]

hi,

have the following tables

book
bookid slotid date carid
1 1 2006/06/02 1
1 2 2006/06/02... 3

mottimes
id mott slotid
1 9:00 1
2 10:00 2
3 11:00 3

am making a query that lists All mot times in a table
and puts the car that is having an mot in the correct slot.

so far...

motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motslot = book.slotid "_
& "LEFT JOIN car ON book.carid = car.carid "_
& "WHERE dayid =1 AND bayid =1 AND book.bookdate =" & [motdate]

now if i take out 'AND book.bookdate =" & [motdate] '
it works fine.
the thing is even if there are no cars booked for [motdate]
i still want it to list all times in mottimes thats why i used left joins.
at the moment i get an eof error.

 

[mbrooks]

If you get an error it's because the date is not quoted.

Example: AND book.bookdate = '[motdate]'

M. Brooks

http://mbrooks.info

 

[TonyGroves]

You would also need to ensure that the date is supplied in the form 'yyyy-mm-dd'.

 

[burnside]

thanks for your answers,

i am using asp to produce the page.
even if there are no bookings for book.bookdate then i still want to list all mott - but if there are no bookings for this date then nothing is listed.

also do you know a function that changes the standard date format to yyyymmdd?

 

[TonyGroves]

I don't quite follow your requirements and what the problem is; can you explain a bit more?

do you know a function that changes the standard date format to yyyymmdd?

Do you mean convert a DATE value to yyyymmdd? Just cast it to an integer:[tt]
datefld+0[/tt]

 

[burnside]

thanks tony,

this table lists all mot times in a day
mottimes
id mott slotid
1 9:00 1
2 10:00 2
3 11:00 3...

& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motslot = book.slotid "_
& "WHERE dayid =1 AND bayid =1"

there are left joins so that if there is no booking the
times are still listed.
however if i put this in the where statement

& "WHERE mot.dayid =1 AND mot.bayid =1 AND book.bookdate = '[datesql]'"

it will only list times where there is an entry in table book which has a date that matches datesql.
I mot to list all mottimes regardless if there is a record in table book.

also my date formats look like 06/06/2006 and i need them to look like 20060606 - is there are function to convert them? [thats an asp q not an sql q!]

 

[guelphdad]

show your entire query, my guess is that the where clause turns your outer join into an inner join.

also please note that at the top you told us your dates were of yyyymmdd format and they are not.

Do you want to convert your dates permanently? you can use str_to_date in mysql to reformat them either permanently by changing your column type from varchar to date type and issuing an update using str_to_date or you can just use it in a select if you somehow want to continue storing your dates in varchar format for some reason.

 

[burnside]

hi guelphdad - thanks for your reply

you are quite right i have mis-typed the dates as stored in the table in date format.
they do need to be in the format yyyymmdd for the query.

the other thing is what you said bout where clause turning outer join into an inner join

motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motslot = book.slotid "_
& "LEFT JOIN car ON book.carid = car.carid "_
& "LEFT JOIN cus ON car.cusid = cus.cusid "_
& "WHERE mot.dayid =1 AND mot.bayid =1 AND book.bookdate = '[datesql]'"

with my limited knowledge i think i might be doing this all wrong - i cant have a query with a WHERE date clause when records do not actually exist [which is what i want]

is this a programming issue or is there some way mysql will do what i want

 

[burnside]

...that is to list all times in mottimes and if there are records that match the date part then list them

 

[burnside]

have changed the sql to this

motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid "_
& "FROM book "_
& "LEFT JOIN mottimes AS mot ON mot.motid = book.slotid "_
& "INNER JOIN car ON car.carid = book.carid "_
& "INNER JOIN cus ON cus.cusid = car.cusid "_
& "WHERE mot.dayid =1 "_
& "AND mot.bayid =1 "_
& "AND book.bookdate = 20060604"

but still cant get mottimes.mott to list - ne ideas welcome

mottimes
id mott slotid
1 9:00 1
2 10:00 2
3 11:00 3

 

[burnside]

this sql lists the data as i would like
except it shows all entries in table book
i would like it to only show records in table book that have the date i specify - but at the same time list all times that are in mott

SELECT mot.mott, mot.motslot, book.bookdate, mot.bayid
FROM mottimes AS mot
LEFT JOIN book ON mot.motid = book.slotid
WHERE mot.dayid =1 
AND mot.bayid =1 
[code]

results

8:15	2	2006-06-04	1
8:15	2	2006-06-07	1
9:30	4	\N	1
10:45	6	\N	1
12:00	8	\N	1
13:45	11	\N	1
15:00	13	\N	1
16:15	15	\N	1
17:30	16	\N	1

 

[guelphdad]

change:

WHERE mot.dayid =1
AND mot.bayid =1

to:

AND mot.dayid =1
AND mot.bayid =1

leaving it as a where clause as you have done means that the info from the two tables must match and thus you are discarding any non-matches.

Now just add another AND clause that specifies your DATE or your DATE RANGE.

 

[burnside]

thanks guelphdad - nearly there
but i need it to only display mot.dayid that equal 1 and mot.bayid = 1
it lists all mottimes regardless of bayid and dayid.
but it is better - didnt know you could leave out WHERE
does that make sense?

 

[burnside]

got it! thanks so much heres a *

motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "AND book.bookdate = 20060604 "_
& "WHERE mot.dayid =1 "_
& "AND mot.bayid =1

 

[r937]

change this --

& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "AND book.bookdate = 20060604 "_

to this --

& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = 20060604 "_
& "LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_


r937.com | rudy.ca

 

[burnside]

thanks r937 - makes sense now