Results that match *all* results in another table

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

SQL Server 2000 Forums

Transact-SQL (2000)

Results that match *all* results in another table

Author

Topic

davenims
Starting Member

21 Posts

Posted - 2006-12-20 : 06:11:29

Hello, I have a bit of a problem, I wonder if anyone can tell me whether the following is possible in a T-SQL query and steer me in the right direction, preferably without resorting to cursors:

Say I have some products which are:
- Red product
- Black Product
- Blue Product
- Green Product

And the packages which are for sale are:
- Red Product (£5)
- Black Product (£5)
- Red Product & Black Product (£7.50)
- Blue Product (£5)
- Green Product (£10)
- Red Product & Green Product (£12)

The lines on the order are entered separately, and I want to find the price of the appropriate package.

So, for example, lines are entered on an order for Black Product and Red Product. The correct price would be the Red Product & Black Product package at £7.50.

Am I on the right track thinking I need to use a query with a HAVING... IN... clause?

Everything I have tried so far will only return the following results:
- Red Product
- Black Product
- Red & Black Product

Whereas I only want the latter package returned in this case.

In another example, say lines were entered for Blue Product, Black Product and Red Product, then the results I would want returned for the packages would be:
- Blue Product
- Red & Black Product.

Any help much appreciated!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 07:15:50

How can we help you write sql when we have no idea what your table structure is?

- Jeff

davenims
Starting Member

21 Posts

Posted - 2006-12-20 : 07:51:28

Hi Jeff, sorry, the table structure is below. I didn't put anything on as I can change the table structure depending on what's required, I'm really just after pointing in the right direction of the way to constrcut the query.

I would have something like the following:

tblProducts
-----------
ProductID INT (PK)
ProductName VARCHAR

example:
(1) - Red Product
(2) - Black Product
(3) - Blue Product
(4) - Green Product

tblPackages
-----------
PackageID INT (PK)
PackageName VARCHAR
PackagePrice MONEY

example:
(1) - Red Product Package - £5
(2) - Black Product Package - £5
(3) - Red Product & Black Products Package - £7.50
(4) - Blue Product Package - £5
(5) - Green Product Package - £10
(6) - Blue Product & Green Product Package - £12

tblProductPackage
------------------
ProductPackageID INT (PK)
ProductID INT
PackageID INT

example:
(1) - 1 - 1 (i.e. Red Product, Red Product Package)
(2) - 2 - 2 (i.e. Black Product, Black Product Package)
(3) - 1 - 3 (i.e. Red Product, Red Product & Black Product Package)
(4) - 2 - 3 (i.e. Black Product, Red Product & Black Product Package)
.... etc ....

One product can belong to many packages

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-12-20 : 07:53:25

Also, what happens if you have an order for, for example, Red product, black product and green product?

Mark

davenims
Starting Member

21 Posts

Posted - 2006-12-20 : 08:03:14

OK, in that situation it should select:
Red & Black Product
Green Product

Basically, it should select as few packages as possible, i.e. selecting packages of more than one product where appropriate, but if not then just the package with the single product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources