are nested tables the answer? 1

[mthompo]

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

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

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

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

 

[r937]

before you do any unnecessary grouping, make sure you are getting the correct rows returned from the join

conditions on a table on the right side of a LEFT OUTER JOIN should go into the ON clause, not the WHERE clause

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 tran.tranid = CC.tranid 
LEFT 
  JOIN courtcarbook AS CB 
    ON CB.carid = CC.carid 
   [b]AND CB.bookst <= '20061015' 
   AND CB.bookend >= '20061021' [/b]
LEFT 
  JOIN cus 
    ON cus.cusid = CB.cusid 
 WHERE CC.display = 0 
ORDER 
    BY CC.carid
     , CB.bookst

r937.com | rudy.ca

 

[mthompo]

thanks for your reply r937
your idea has made it better - not all rows in week
have multiple rows per vehicle [number of rows = number of bookings for vehicle]

now only the weeks that have bookings in any month
have multiple vehicle rows

if i group on CC.carid it only shows the first booking in the table

any suggestions to get to the bottom of this are welcome

sql

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"_ 
& " AND CB.bookst >= '" & dstartdate & "' AND CB.bookend <= '" & denddate & "'" _
& " LEFT JOIN cus ON cus.cusid = CB.cusid"_
& " WHERE CC.display = 0"_	
& " ORDER BY CC.carid, CB.bookst"

 

[r937]

explain please why you want to use GROUP BY

r937.com | rudy.ca

 

[mthompo]

if i use groupby the multiple rows per vehicle
problem goes away - but i dont get all
my bookings! [which is more important]

i am grasping at straws and dont know what the next stage
to sort this out is

 

[r937]

if there are multiple booking per vehicle, which one do you want?

alternatively, if you don't want a particular individual booking, why do you have those three CB columns in the SELECT?

r937.com | rudy.ca

 

[mthompo]

there is another issue with the date AND clause above
if a booking does not fit between the start and end date
then it is not shown
so if a booking lasts longer than a week the page does not show the booking

 

[r937]

so if a booking lasts longer than a week the page does not show the booking"

and what would you like to do about this?

r937.com | rudy.ca

 

[mthompo]

the page should look like

March
reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH SMITH SMITH FREE BOB BOB BOB
HJ55TFG FREE FREE FREE FREE FREE FREE
HJ06UYH FREE FREE FREE FREE FREE FREE

but if i go to February 1st I get

February
reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH FREE FREE FREE FREE FREE FREE
HJ06TYH FREE FREE FREE FREE FREE FREE
HJ55TFG FREE FREE FREE FREE FREE FREE
HJ06UYH FREE FREE FREE FREE FREE FREE

if bobs booking went till mon9th then, with the date clause, his booking disapears

would a working example help to explain it better

 

[r937]

that doesn;t look anything remotely like your query --

SELECT cus.lname
, CC.reg
, CB.bookst
, CB.bookend
, CC.carid
, CB.courtcarbookid
, tran.trandesc
, CC.model
FROM ...

r937.com | rudy.ca

 

[mthompo]

ok - have narrowed it down a bit
what i need is conditions that find out if
a)the start of a week is between a bookings start/end
OR
b)the end of a week is between a bookings start/end

so far have

SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid 
FROM courtcarbook AS CB
WHERE '01/02/2006' BETWEEN cb.bookst AND cb.bookend 
OR '07/02/2006' BETWEEN cb.bookst AND cb.bookend
ORDER BY CB.carid, CB.bookst

but i still get records passed back that dont adhere to these conditions

 

[r937]

what about when booking start is between start and end of week, and booking end is also between start and end of week?

these will not be found either by a) or b)

what actually are you trying to return with your main query? because i still don't understand whether you need the actual day of week (MON- FRI)

r937.com | rudy.ca

 

[mthompo]

the days of the week are worked out by the asp

how can i build conditions that select bookings
where the startdate of the week is within cb.bookst and
cb.bookend and then end date of the week is between
cb.bookst and cb.bookend

thought i had it

booksql = "SELECT courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend "_ 
& "FROM courtcarbook "_
& "WHERE courtcarbook.carid = " & x_carid _
& " AND '" & x_bookend & "' BETWEEN bookst AND bookend " _
& "OR courtcarbook.carid = " & x_carid & " AND '" & x_bookst & "' BETWEEN bookst AND bookend " _
& "OR courtcarbook.carid = " & x_carid & " AND bookst BETWEEN '" & x_bookst & "' AND '" & x_bookend &"'"

 

[mthompo]

have changed the sql to this

SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid 
FROM courtcarbook AS CB 
WHERE (cb.bookst BETWEEN '15/10/2006' AND '21/10/2006') 
OR (cb.bookend BETWEEN '15/10/2006' AND '21/10/2006')

but still returns records from 2005!

any ideas welcome

 

[r937]

those are invalid dates



r937.com | rudy.ca

 

[mthompo]

ok - thanks...sure that worked friday!

(SELECT CB.bookst, CB.bookend, CB.carid, 
    CB.courtcarbookid, CB.cusid
    FROM courtcarbook AS CB
    WHERE  (cb.bookst BETWEEN '20061015' AND '20061021')
    OR     (cb.bookend BETWEEN '20061015' AND '20061021')
    OR (cb.bookst < '20061015') 
    AND (cb.bookend > '20061021')

this should include all records
a)the start of a week is between a bookings start/end
b)the end of a week is between a bookings start/end
c)the bookings start before week start and end after week end

seems to work ok - any thing ive missed out

 

[r937]

that looks okay

r937.com | rudy.ca