sql server new users: Hey Homer,

you need data integrety so you dont need products in the client field, this
will produce duplicate data in the database.

Create another table and call it orders...

You want to keep everything to do with the client, products and orders
seperate...

so, you would have a clientID field and a ProductID field in each of the
tables respectively... you would then create and Orders table which would
have 2 colums, ClientID & OrderID... as each client makes an order the orders
table would be used to record this.

This is not the right way to go. Using the info you have provided, you
would have 3 tables:

Clients
ClientID
ClientName

Products
ProductID
ProductName

ClientProducts
ClientID
ProductID
Quantity

Thus, ClientProducts would have the list of all products bought by each
client. If you are interested in which clients have bought both products A
and B, try:


select
ClientID
from
ClientProducts
where
ProductID in ('A', 'B')
group by
ClientID
having
count (*) = 2

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com


[quoted text, click to view]
I'm just starting out with SQL server and I am trying to understand the
following.

I have two tables, say "Clients" and "Products"

A client can buy one or more products so I want to have a "Products" field
in the clients table. which stores the list of products a client has bought.

How do I specify that and if I have a client how can I say that he has
product A and B?

--
Homer Fhardwhoster
"Get 'em while they're 'ot, they're lovely"

One more thing, if you want an example database of these relationships,
check out Northwind, which ships with SQL Server.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com


[quoted text, click to view]
This is not the right way to go. Using the info you have provided, you
would have 3 tables:

Clients
ClientID
ClientName

Products
ProductID
ProductName

ClientProducts
ClientID
ProductID
Quantity

Thus, ClientProducts would have the list of all products bought by each
client. If you are interested in which clients have bought both products A
and B, try:


select
ClientID
from
ClientProducts
where
ProductID in ('A', 'B')
group by
ClientID
having
count (*) = 2

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com


[quoted text, click to view]
I'm just starting out with SQL server and I am trying to understand the
following.

I have two tables, say "Clients" and "Products"

A client can buy one or more products so I want to have a "Products" field
in the clients table. which stores the list of products a client has bought.

How do I specify that and if I have a client how can I say that he has
product A and B?

--
Homer Fhardwhoster
"Get 'em while they're 'ot, they're lovely"

The design you should have is a third table. Let's call that table
Purchases. It has foreign key references to both Client and Product tables,
and most likely a few more columns, like Purchase_date and Quantity:

CREATE TABLE Purchases (
Client_ID INT NOT NULL,
Product_ID INT NOT NULL,
Purchase_date DATETIME NOT NULL,
Quantity INT NOT NULL
)

You can then get a list of all the products create by a client with:
SELECT Product_ID
FROM Purchases
WHERE Client_ID = ...

--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]

I'm just starting out with SQL server and I am trying to understand the
following.

I have two tables, say "Clients" and "Products"

A client can buy one or more products so I want to have a "Products" field
in the clients table. which stores the list of products a client has bought.

How do I specify that and if I have a client how can I say that he has
product A and B?

--
Homer Fhardwhoster
"Get 'em while they're 'ot, they're lovely"

Great, I understand now. Thanks!!

--
Homer Fhardwhoster
"Get 'em while they're 'ot, they're lovely"

[quoted text, click to view]