joining fields from 3 tables

[nutstretch]

I am trying to retrieve data from 3 different tables. one has customer name, one has booking date and status of booking and the third has whether a kennel has been allocated to a booking. table one is linked to table 2 by cust_no and two linked to 3 by booking_no I am trying to find the surname, date of booking of bookings where a kennel has not been allocated to a pet. my code is:

Set rs1 = db.OpenRecordset("select tblCustomer.surname, tblbooking.booking_no, tblbooking.date_in from tblCustomer, tblBooking, tblBooking_details where tblBooking.cust_no = tblCustomer.cust_no and tblbooking.booking_no = tblBooking_details.booking_no and tblBooking_details.kennel_no = ' " & stat & " ' order by surname ")

Please someone tell me what i have domne wrong as i am pulling my hair out.

I am not sure if I need extra quotes or & in the code.

[JHausmann]

The query gets a little easier to read if you use an alias for the table names, as below.

Set rs1 = db.OpenRecordset("select a.surname, b.booking_no, b.date_in from tblCustomer a, tblBooking b, tblBooking_details c where a.cust_no = b.cust_no and b.booking_no = c.booking_no and c.kennel_no = ' " & stat & " ' order by surname ")

is kennel-no a numeric or character based field?

[Mongo]

When is a record assigned to the tblBooking_details table (i.e., are there default null/other-value records for every booking OR no records until the entry of the kennel_no assignment)? This determines whether you want to use a NOT EXISTS or standard search argument. For example:

' Route A
SELECT a.surname, b.booking_no, b.date_in 
FROM tblCustomer a, tblBooking b, tblBooking_details c
WHERE a.cust_no = b.cust_no 
  AND b.booking_no = c.booking_no 
  AND c.kennel_no = [default null/other-value]
ORDER BY a.surname

' Route B
SELECT a.surname, b.booking_no, b.date_in 
FROM tblCustomer a, tblBooking b
WHERE a.cust_no = b.cust_no 
  AND NOT EXISTS(SELECT c.booking_no 
    FROM tblBooking_details c
    WHERE c.booking_no = b.booking_no)  
ORDER BY a.surname

[JHausmann]

It looks like (from the query) that the only reason booking_details is even in the query is to provide selection criteria, which would tend to indicate it's presence is a requirement... Of course, I could be wrong...

I'd be real interested in the plan on this query, just to see how the parser decides how to build the joins...

[nutstretch]

many thanks for Replies. Mongo : I wasn't sure about not exists so added character to field on addittion of the record.

JHausmann : I tried the code which seems the same as already had but using alias' Nothing appeared in my listview. The field kennel_no is character based as there is calculation need to be done on this and there for takes up less memory Am i right?

my listview code is as follows as works on all my other listviews.

Do While Not rs1.EOF

Set itemX1 = ListView2.ListItems.Add
itemX1.Text = rs1("booking_no")
itemX1.SubItems(1) = rs1("surname")
itemX1.SubItems(2) = rs1("date_in")

rs1.MoveNext
Loop

Do I need & before each and in the code.

If i put the query into access then i get what i want so i am sure it has something to do with the syntax of my statement.

many thanks again for you comments

[JHausmann]

Character fields use 1 byte per length. Integer fields use 1, 2 or 4 bytes (in SQL Server) depending on the type of integer specified, Tiny, Small or regular.

Put a breakpoint on your set command and see what the SQL looks like before you try to run it. My guess is that the variable "stat" has an unexpected value in your program

[nutstretch]

the variable stat is a data type string

It is assigned to the same character which is given to the kennel_no at booking

[JHausmann]

do this:

1) change the code

dim sSQL as string
sSQL="select a.surname, b.booking_no, b.date_in from tblCustomer a, tblBooking b, tblBooking_details c where a.cust_no = b.cust_no and b.booking_no = c.booking_no and c.kennel_no = ' " & stat & " ' order by surname "
Set rs1 = db.OpenRecordset(sSQl)

2) set a breakpoint on the set rs1 line
3) run your prog from VB
4) when it stops at the breakpoint, type (without the quotes) in the immediate window:

"?sSql"
5) copy the entire contents of the immediate window into a response to this post

[nutstretch]

Hi Thanks for helping me. This is what arrived in the immediate window.


select a.surname, b.booking_no, b.date_in from tblCustomer a, tblBooking b, tblBooking_details c where a.cust_no = b.cust_no and b.booking_no = c.booking_no and c.kennel_no = ' ' order by surname

[JHausmann]

You have no value for stat when the recordset is set. I'd guess because it's a local variable.