Select common data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select common data

Author

Topic

duffyciaran
Starting Member

1 Post

Posted - 2007-09-21 : 18:28:12

Hi - I'm trying to construct a select statement from the following tables(see example)

_________________________________
|Customer | Product | Shop |
|----------|----------|---------|
|Customer1 | Milk | Dairy |
|Customer2 | Cream | Dairy |
|Customer3 | Milk | Dairy |
|Customer1 | Trainers | Sports |
|Customer2 | Football | Sports |
_________________________________

REQUIRED RESULT SET: Milk, Cream.

Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.

This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:

CUSTOMER:
customerID

PRODUCT:
productID
customerID
shopID

SHOP:
shopID

This looks like it should be easy but my SQL isn't the best ;-)

I'd really appreciate any help you could give!!

Cheers!

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-21 : 23:23:24

One way you could do it.

DECLARE @count int
SELECT @count = count(customerID) from customer

SELECT distinct productID from Product where shopID in
(
SELECT shopID from Product
Group BY shopID
having count(customerID) = @count
)

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources