Retrieving data from 4 tables

[bsheridan]

Hello,I am trying to retrive data from 4 tables called the following:

• Client
• Staff
• Treatment
• Sale

The Sale table holds foreign keys from the 3 other tables i.e:

SALE.clientID, SALE.staffID and SALE.treatmentID

What I want to do is be able to display data from tables Client, Staff and Treatment (which are obviously dependent on an entry in the Sale table) by using an SQL join on the SALE table. For example I want to be able to display a history of sales for a specific client which will show, the client's name, the treatment they had and the staff member who did the treatment.I reckon i should use Inner joins but the code below is not displaying anything:

strSQL = "SELECT * From CLIENT INNER JOIN (SALE INNER JOIN (TREATMENT INNER JOIN STAFF" & _	"ON TREATMENT.treatmentID = SALE.treatmentID ) ON CLIENT.clientID = SALE.saleID " & _	"WHERE (( (CLIENT.clientID)="& Request.QueryString("clientID") & "));"

Can you help?Thanks,Bernard.

[bsheridan]

Ok,After reviewing the code again i amended it to this:

strSQL = "SELECT * From (((SALE " & _	" Inner Join CLIENT On SALE.clientID = CLIENT.clientID)" & _	" Inner Join TREATMENT On SALE.treatmentID = TREATMENT.treatmentID)" & _	" Inner Join STAFF On SALE.staffID = STAFF.staffID)" & _	" WHERE  (((CLIENT.clientID) = " & Request.QueryString("clientID") & "));"

but it only lets me display the saleID when i try to return the record sets from the tables.Again, any help appreciated.Thanks,Bernard.

[bsheridan]

The error wasn't in the SQL code after all, it was in the ASP code that i was using to diaplsy the record sets. I have 2 tables (staff and client) and in both tables i had a column called "firstName". So i just changed the columns to "staffFirstName" and "clientFirstName"... so now it works