SQL problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL problem

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-08-19 : 20:11:41

Hi,
I have two tables:

PACKAGES
PackageID-----ProductCode
1-------------Test101
1-------------Test102

CARTS
PackageID-----ProductCode
1-------------Test101

How can I select items in Packages that are not in Carts that have the same PackageID? So I would like to return Test102. Thanks

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-08-19 : 21:34:56

Hi,
Will
SELECT * FROM Packages INNER JOIN Carts ON Carts.PackageID <> Packages.PackageID
do?
Or you can do
SELECT * FROM Packages WHERE PackageID NOT IN(SELECT PackageID FROM Carts) if the Carts table is not very large.

Sarah Berger MCSD

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-20 : 20:07:24

You TALK about PackageID, but the sample data you show says that you want to return ProductCodes that don't match... So, based on your sample data, try this:

SET NOCOUNT ON
CREATE TABLE #Packages (
PackageID int,
ProductCode varchar(10)
)
GO

CREATE TABLE #Carts (
PackageID int,
ProductCode varchar(10)
)
GO

INSERT INTO #Packages VALUES (1, 'Test101')
INSERT INTO #Packages VALUES (1, 'Test102')

INSERT INTO #Carts VALUES (1, 'Test101')
GO

SET NOCOUNT OFF

SELECT *
FROM #Packages P
WHERE P.ProductCode <>
(
SELECT C.ProductCode
FROM #Carts C
WHERE C.PackageID = P.PackageID
)

DROP TABLE #Packages
DROP TABLE #Carts

The part in red is the key, the rest is just sample tables to test my work. You'll need to test this with a larger set of data to determine if it is restrictive enough. It works with the minute dataset you provided though.

Edited by - ajarnmark on 08/20/2002 20:08:01

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources