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.
' Get the Product Information
sqlString = "SELECT * FROM Product "
sqlString = sqlString & "WHERE product.product_id=" & productID
Set RS = Server.CreateObject( "ADODB.Recordset" )
RS.ActiveConnection = Con
' 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
if ( cat = 2 ) THEN
sqlString = "SELECT Author, ISBN, Publisher, Pages FROM BOOK WHERE book.product_id = " & productID
IF ( cat =3) THEN
sqlString = "Select Developer, System, Genre From Game Where game.product_id = " & productID
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
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:
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.
If cat = 1
select 1, 2 , 3
from product join book on p.product_id= book.product_id
where product_id= & productid
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.
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.