hi,
am having trouble getting my asp page to display correctly.
i am also new to nested tables in sql so need your help.
if i use this query
it will produce the page i want when records exist in the
table CB and CB.bookst of the record are the current date
i have been told this is because i need to put the where condition in a nested select for the join to the bookings table.
At the moment, i am making a big dataset and then filtering on that - which means the existing car id's are removed due to the where condition not being met.. kind of negating the left joins.
so i added the between date WHERE statement and tried to
Group on CC.carid
which produces
i am really stuck if any asp/myql developers are reading this plese refer to thread333-1268414 near the bottom
and thanks again to Damber
am having trouble getting my asp page to display correctly.
i am also new to nested tables in sql so need your help.
if i use this query
Code:
SQL ="SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model "_
& "FROM courtcar AS CC "_
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid"_
& " LEFT JOIN cus ON cus.cusid = CB.cusid"_
& " WHERE CC.display = 0"_
& " ORDER BY CC.carid, CB.bookst"
it will produce the page i want when records exist in the
table CB and CB.bookst of the record are the current date
i have been told this is because i need to put the where condition in a nested select for the join to the bookings table.
At the moment, i am making a big dataset and then filtering on that - which means the existing car id's are removed due to the where condition not being met.. kind of negating the left joins.
so i added the between date WHERE statement and tried to
Group on CC.carid
Code:
SQL ="SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model "_
& "FROM courtcar AS CC "_
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid"_
& " LEFT JOIN cus ON cus.cusid = CB.cusid"_
& " WHERE CC.display = 0"_
& " AND CB.bookst >= " & dstartdatesql & " AND CB.bookend <= " & denddatesql & "" _
& " GROUP BY CC.carid"_
& " ORDER BY CC.carid, CB.bookst"
which produces
Code:
SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model FROM courtcar AS CC LEFT JOIN tran ON CC.tranid = tran.tranid LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid LEFT JOIN cus ON cus.cusid = CB.cusid WHERE CC.display = 0 AND CB.bookst <= '20061015' AND CB.bookend >= '20061021' GROUP BY CC.carid ORDER BY CC.carid, CB.bookst
i am really stuck if any asp/myql developers are reading this plese refer to thread333-1268414 near the bottom
and thanks again to Damber