Weird Query Help - 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

SQL Server Development (2000)

Weird Query Help

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-01-27 : 18:04:48

Gotta love marketing for comming up with this one. I can't for the life of me figure this out!!

We need to find the distinct customers that have a 'Web Site' but do not have 'T.Link'. We are trying to get this to happen dynamically in Fox Pro, but if I can find a solution in T-SQL, I should be able to convert it to work in VFP.

Thanks for any help guys and gals!



CREATE TABLE #Customer(CustomerName VARCHAR(50), CustNum VARCHAR(20))
CREATE TABLE #Product(CustNum VARCHAR(20), ProductName VARCHAR(50))

INSERT INTO #Customer(CustomerName, CustNum) VALUES('1 One', '111111')
INSERT INTO #Customer(CustomerName, CustNum) VALUES('2 Two', '222222')
INSERT INTO #Customer(CustomerName, CustNum) VALUES('3 Three', '333333')
INSERT INTO #Customer(CustomerName, CustNum) VALUES('4 Four', '444444')
INSERT INTO #Customer(CustomerName, CustNum) VALUES('5 Five', '555555')
INSERT INTO #Customer(CustomerName, CustNum) VALUES('6 Six', '666666')

INSERT INTO #Product(CustNum, ProductName) VALUES ('111111', 'Web Site')
INSERT INTO #Product(CustNum, ProductName) VALUES ('222222', 'Web Site')
INSERT INTO #Product(CustNum, ProductName) VALUES ('222222', 'T.Link')
INSERT INTO #Product(CustNum, ProductName) VALUES ('333333', 'T.Link')
INSERT INTO #Product(CustNum, ProductName) VALUES ('444444', 'Web Site')
INSERT INTO #Product(CustNum, ProductName) VALUES ('555555', 'Web Site')
INSERT INTO #Product(CustNum, ProductName) VALUES ('555555', 'T.Link')
INSERT INTO #Product(CustNum, ProductName) VALUES ('555555', 'HouseCalls')


--Find Distinct Customers that have 'Web Site' but do not have 'T.Link'
--Should be only Two and Five


DROP TABLE #Product
DROP TABLE #Customer

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 18:22:04

Shouldn't the answer be 1 and 4?

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-01-27 : 18:35:07

Tara's right.

This gives 1 and 4


SELECT * 
from #Product P
WHERE ProductName = 'Web Site'
	AND NOT EXISTS(SELECT 1 from #Product WHERE CustNum = P.CustNum AND ProductName = 'T.Link')

Remove the NOT keyword to get 2 and 5

DavidM

"SQL-3 is an abomination.."

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-01-27 : 19:02:36

Opps You are right, and I think that David got it!

Thanks man! I knew it had to be something simple like that. You should have seen the ways we were trying to attack this!

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources