Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Question Unanswered: Data from 2 tables in 1 RS and sort?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select cart.cart_ID
         , cart.order_ID
         , cart.product_ID
         , cart.product_width
         , cart.product_length
         , cart.unit_price
         , cart.quantity
         , cart.line_total 
         , products.product_name
      from cart
    inner
      join products 
        on cart.product_ID
         = products.product_ID
     where cart.order_ID = n
    see Understanding SQL Joins and pay no attention to the MySQL in the title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Ok, I will do.. and I will give that ago what you have put.

    thank you for such a prompt reply!

  4. #4
    Join Date
    Oct 2004
    Posts
    7

    Thumbs up

    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:

    Code:
        '## 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!
    Last edited by dibley; 10-31-04 at 19:15.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2004
    Posts
    7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •