many to one relationship

[James]

How do I set up the tables and relationship such that I
can reflect the item desc. of all items in my shopping
cart? I am having item1, item2,..., itemN in my shopping
cart which all join to itemID in the items table. Since I
have multiple items in the shopping cart how does sql know
which item I am requesting

[Ken Schaefer]

SELECT
b.ItemID
, b.ItemDescription
FROM
Items AS b
INNER JOIN
Basket AS a
ON
a.ItemID = b.ItemID
WHERE
a.BasketID = <....>

<....> is the BasketID that you have assigned to the particular user.

Cheers
Ken


"James" <jtnchang@hotmail.com> wrote in message
news:065601c3943c$fc992770$a101280a@phx.gbl...
: How do I set up the tables and relationship such that I
: can reflect the item desc. of all items in my shopping
: cart? I am having item1, item2,..., itemN in my shopping
: cart which all join to itemID in the items table. Since I
: have multiple items in the shopping cart how does sql know
: which item I am requesting

[jc123]

Thanks Ken. So instead of itemID1, itemID2,... I would just have itemID
and as long as long as I have my while not rs.eof loop it will get all
the items that meet the criteria. so the problem with my tables design
is that I designed the basket table with item1, qty1, item2, qty2,.... I
should have just one item and one qty but in the form I would make
provision for multiple pairs and do a while request("item")<> "" to
retrieve the pair, one at a time. Thanks very much for your
enlightenment.

jc123

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand - MVP]

> the items that meet the criteria. so the problem with my tables design

> is that I designed the basket table with item1, qty1, item2, qty2,...

Yes! This is not very well designed at all. If I want to add 40 items to
my cart, are you going to have to alter the table to accommodate me?
item40, qty40, etc? *shudder*

[Tom B]

I think what you really want is a many to many relationship.
Three tables....

Table1 is your items table which describes individual items
Table2 is your shopping cart which would describe a cart
Table3 is your cart details (the joining table) which would likely only have
three columns - the unique cart id, the unique item id and the quantity of
items.

Ken's query implies Table 2

"James" <jtnchang@hotmail.com> wrote in message
news:065601c3943c$fc992770$a101280a@phx.gbl...

> How do I set up the tables and relationship such that I
> can reflect the item desc. of all items in my shopping
> cart? I am having item1, item2,..., itemN in my shopping
> cart which all join to itemID in the items table. Since I
> have multiple items in the shopping cart how does sql know
> which item I am requesting

[Aaron Bertrand - MVP]

When the order is placed, you probably also want to store price, weight,
shipping information etc. so that when users or administrators review orders
from history, even AFTER prices have changed or container sizes have
increased/decreased, they see their order as it was, not as it would be if
they placed it today.




"Tom B" <shuckle@hotmail.com> wrote in message
news:#E2MTtLlDHA.1740@TK2MSFTNGP12.phx.gbl...

> I think what you really want is a many to many relationship.
> Three tables....
>
> Table1 is your items table which describes individual items
> Table2 is your shopping cart which would describe a cart
> Table3 is your cart details (the joining table) which would likely only

have

> three columns - the unique cart id, the unique item id and the quantity of
> items.

[Tom B]

A good point. One that's tripped me up before. If it can change in the
future.....store it now.
There's a good example here

[url]http://www.aspfaq.com/cart/[/url]

It only uses two tables(plus one for the customer), but still uses the
many-to-many relationship for the cart.

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:O3WRZQMlDHA.2444@TK2MSFTNGP09.phx.gbl...

> When the order is placed, you probably also want to store price, weight,
> shipping information etc. so that when users or administrators review

orders

> from history, even AFTER prices have changed or container sizes have
> increased/decreased, they see their order as it was, not as it would be if
> they placed it today.
>
>
>
>
> "Tom B" <shuckle@hotmail.com> wrote in message
> news:#E2MTtLlDHA.1740@TK2MSFTNGP12.phx.gbl...

> > I think what you really want is a many to many relationship.
> > Three tables....
> >
> > Table1 is your items table which describes individual items
> > Table2 is your shopping cart which would describe a cart
> > Table3 is your cart details (the joining table) which would likely only

> have

> > three columns - the unique cart id, the unique item id and the quantity

of

> > items.

>
>