Linking Multiple Tables in an SQL Query

Hello,

I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.

So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using...

SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @.CartID ORDER BY ProductID

I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?

I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!

I'd really appreciate any help you can offer me.

Many thanks,

Luke

I don't think you should use just one or two tables to create a shopping cart, the commerce starter kit from Microsoft shopping cart is seven tables, you may want to just clone those and use because it includes all the stored proc you will need. Click on the commerce starter kit white paper for details. Hope this helps.

http://www.asp.net/downloads/default11.aspx?tabid=62