Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: Help with a Select Statement

    Hey there guys,

    I'm gonna go ahead and put it out that I am a novice user and currently a student. I'm having an issue when it comes to selecting from multiple tables. I've tried UNION's and JOINS but can't seem to get it right. To give you a description of what I am trying to do, I have a website that I have made. It's essentially a E-Commerce site, What I am trying to do is have it so when a customer selects a product and the website displays it, It will display items for that particular product that are stored in its table.

    Currently my database is set up with a Product supertype table and 3 sub tables which are Book, DVD, and Game. With each subtable containing unique items for their product type. This is the code I am currently using in ASP. With that code the Issue is that when I pull data for the specific cat that data is not in the RS as I am only querying the Product Table. Really the closest I get to solving is: Select * from product, book, dvd, game where product_ID= "& productid but then all the information is set to the same product and it just makes it worse.

    Code:
    ' Get the Product Information
    sqlString = "SELECT *  FROM Product "
    sqlString = sqlString & "WHERE product.product_id=" & productID 
    Set RS = Server.CreateObject( "ADODB.Recordset" )
    RS.ActiveConnection = Con
    RS.Open sqlString
    
    ' Get Current Category
    cat = RS( "product_type" )
    
    
    'Get information for each of the categories
    if ( cat = 1 ) THEN
    	sqlString = "Select Studio, Director, Genre, Rating, Duration From DVD WHERE dvd.product_id= " & productID
    End IF
    if ( cat = 2 ) THEN
    	sqlString = "SELECT Author, ISBN, Publisher, Pages FROM BOOK WHERE book.product_id = " & productID
    End IF
    IF ( cat =3) THEN
    	sqlString = "Select Developer, System, Genre From Game Where game.product_id = " & productID
    End if
    IF (cat =4) THEN
    	sqlString = "Select Product_Condition, Customer.F_Name, Customer.L_Name, Customer.UserName FROM LawnSale inner join Customer on LawnSale.Customer_ID=Customer.Customer_ID where lawnsale.product_id= " & productID
    End IF
    Con.Execute sqlString

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Forgive me but I do not understand.
    when a customer selects a product and the website displays it, It will display items for that particular product that are stored in its table.
    the Issue is that when I pull data for the specific cat[egory] that data is not in the R[ecord]S[et] as I am only querying the Product Table
    It might be best if you give your table definitions and a description of how they relate along with some sample data and expected results.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    Sorry was in a rush and didn't proof read my message. Let me see if I can explain this, I apologize cause I suck at explaining this stuff.

    As far as Relationships go between the tables.

    Its a super type sub type relationship between Product and Games, DVD, and Books. In which Product's Primary Key which is Product_ID is used as the PK in each of the subtype tables. Here's a photo of the relationship I took in SQL:

    http://i747.photobucket.com/albums/x...tionship-2.jpg

    Here are a few sample data entries that I have:

    (I just queried for data out of the product table and the game table)
    http://i747.photobucket.com/albums/x...elp/sample.png


    Please let me know if you need more info, and I apoligize again for my novici-ness

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Okay, that helps a little with understanding the background.

    Now on to your question...
    ...what's your question?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    Sorry was really busy and couldn't get on here to answer your question. Well basically I was wondering if there would be a way to maybe do a conditional join statement. Or if there was a way to select everything in these 4 tables.

    Like

    If cat = 1
    select 1, 2 , 3
    from product join book on p.product_id= book.product_id
    where product_id= & productid
    end else
    if cat = 2
    (and so on)


    See the problem I am having is not having objects in my record set that I will later be using for displaying product data.

  6. #6
    Join Date
    Mar 2012
    Posts
    4
    Nevermind I went another path. I was trying to do this all in one recordset. Just made life easy on myself by using multiple record sets and it works fine now. Thanks for taking a look at what I had though.

Posting Permissions

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