nested query 1

[thompom]

Hi, i have a main table that stores lookup values to other tables such as customer, car etc...

however one lookup table is a many-to-many relationship so it uses a link table called stdworklink but even though the link table has two foreign key records from the main table my query displays only one result.

do i need to use a nested query - have started but is still only get one result, any ideas welcome

SELECT mot.mott, book.carid, car.reg, car.modelid, make.make, users.user, book.bookid, book.bookdate, 
book.waittick, book.booknotes, cus.fname, cus.lname, cus.pcode, cus.add1, cus.hphone, cus.wphone, 
cus.mphone, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, book.oldcar, SLL.stdworkid 
FROM mottimes AS mot 
LEFT JOIN book ON mot.motid = book.slotid AND book.bookdate = 20070928 
LEFT JOIN car ON car.carid = book.carid 
LEFT JOIN cus ON cus.cusid = car.cusid 
LEFT JOIN make ON make.makeid = car.makeid 
LEFT JOIN users ON users.userid = book.userid 
LEFT JOIN 
(SELECT SL.stdworkid, SL.bookid FROM stdworklink AS SL)
AS SLL
ON book.bookid = SLL.bookid 
WHERE mot.dayid =1 AND ((mot.bayid = 1 AND mot.baytypeid =2) 
or (mot.bayid = 2 AND mot.baytypeid =2)) 
GROUP BY mot.motid 
ORDER BY mot.motid, mot.mott, mot.bayid, mot.baytypeid

 

[r937]

change this --

LEFT JOIN
(SELECT SL.stdworkid, SL.bookid FROM stdworklink AS SL)
AS SLL
ON book.bookid = SLL.bookid

to this --

LEFT JOIN stdworklink as SLL
ON book.bookid = SLL.bookid

i don;t uderstand why you said that stdworklink has two foreign keys from the mot table, i see only one

r937.com | rudy.ca

 

[thompom]

hi - thanks for your reply, however i still am not getting the results i need - the changes made no difference to the results

at the moment i get all times in mot with any bookings next to the time e.g

mot.mott  cus.fname  cus.lname  sll.stdworkid  book.bookid
9:00 am    (NULL)     (NULL)      (NULL)       (NULL)
10:00 am     M         SMITH        132          1
11:00 am   (NULL)    (NULL)       (NULL)       (NULL)
12:00 am     T        JONES         121          2

now i know that there are two records in table sll with id 1, so i would expect the results to look like

mot.mott  cus.fname  cus.lname  sll.stdworkid  book.bookid
9:00 am    (NULL)     (NULL)      (NULL)       (NULL)
10:00 am     M        SMITH        132          1
10:00 am     M        SMITH        187          1
11:00 am   (NULL)     (NULL)      (NULL)       (NULL)
12:00 am     T        JONES        121          2

i want this so i dont have to open a new recordset and another loop to display all jobs for that customer.
if im not making sense please let me know!

 

[r937]

the reason you are getting only one row per mot is due to this --

GROUP BY mot.motid

why are you grouping?

r937.com | rudy.ca

 

[thompom]

thanks r937 - that was it!