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?
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.
But using that article that you posted the link to, I have modified my original and tested as well and come up with:
'## 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_name, products.product_type "
strSQL = strSQL & "FROM cart, products "
strSQL = strSQL & "WHERE ((cart.order_ID) = " & Session("order_ID") & " ) AND (products.product_ID = cart.product_ID) "
strSQL = strSQL & "ORDER BY cart.cart_ID"
strSQL = strSQL & ";"
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!
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.