Hi,
Up until now, I have never needed to extract data from 2 tables into a recorset and present the data in a controlled manner. I have use the site search facility but the only examples I have seen use left join etc. which I can't get my head around. Please could any one help or point me to a topic which explicitly covers this in a beginners manner?

The 2 table I have are as below.

Table: products
product_ID
product_name
product_catagory_ID
product_etc.....

Table: cart
cart_ID
order_ID
product_ID
etc....

What I would like to be able to do would be to get all the records from cart where cart.order_ID = (VALUE held in SESSION object) and pull the product name from products (products.product_name) from products for each cart.product_ID in cart and display correct product name for each product in the cart with the other fields in the cart table.

So far my SQL statement is as follows but I don't know how to make it grab the name from products, or how to display it corectly after.

'## Select from data base record to show
strSQL = "SELECT cart.cart_ID, cart.order_ID, "
strSQL = strSQL & "cart.product_ID, cart.product_width, "
strSQL = strSQL & "cart.product_length, cart.unit_price, "
strSQL = strSQL & "cart.quantity, cart.line_total "
strSQL = strSQL & "FROM cart, products "
strSQL = strSQL & "WHERE ((cart.order_ID) = " & Session("order_ID") & " )"
strSQL = strSQL & ";"


I assume I need to do something along the lines of

'## Select from data base record to show
strSQL = "SELECT cart.cart_ID, cart.order_ID, "
strSQL = strSQL & "cart.product_ID, cart.product_width, "
strSQL = strSQL & "cart.product_length, cart.unit_price, "
strSQL = strSQL & "cart.quantity, cart.line_total, "
strSQL = strSQL & "products.product_ID, products.product_name "
strSQL = strSQL & "FROM cart, products "
strSQL = strSQL & "WHERE ((cart.order_ID) = " & Session("order_ID") & " ) AND products.product_ID = cart.product_ID"
strSQL = strSQL & ";"

Is this possible and can anybody suggest a method to do it?

Any help appreciated.

see Understanding SQL Joins and pay no attention to the MySQL in the title

Ok, I will do.. and I will give that ago what you have put.

thank you for such a prompt reply!

Yep, yours works perfect!

But using that article that you posted the link to, I have modified my original and tested as well and come up with:

that also appears to do the same job

(please note: products.product_type has been added since my first post as well!)

Thank you r937,

Thanks to you I now have two ways to do this where before hand I had none. Cheers! I am keeping the other example commented out in my code as an alternative example.

Out of interest, is any one of the ways a better way of writing than the other, just so I know for thew future. Also are any of these methods access specific or will both work equally well with mysql. Not that I'm using mysql, but it would be nice to know for the future!

it is far better to learn JOIN syntax than to continue using table list format (which has the tables in a comma-separated list in the FROM clause, and the join conditions in the WHERE clause)

you will need to use JOIN syntax for outer joins anyway

Well, i will look a little further into it now I have learnt the first stage. I must confess I have in the past used the bad practice of running round the houses with the asp code to get the results I am looking for, but I am sure using JOINS will be a much simpler and faster way, now.

Thanks again R937.