It seems so simple, but the join won't work!

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)

It seems so simple, but the join won't work!

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-01-11 : 20:40:41

I have what seems like it should be such a simple query, but apparently I'm doing something wrong. I'm trying to perform a query to bring back all the results that do not exist in another table, but instead I'm getting all results in the table.

 
Select
	CountryCode
From
	tblUserAccounts UA
	Inner Join tblListerAcceptedCountries LAC
		On UA.ListerID = LAC.ListerID
	Inner Join tblUserAcceptedCountries UAC
		On UA.UserAccountID = UAC.UserAccountID AND LAC.CountryCode != UAC.CountryCode
Where
	UA.UserAccount = @UserAccount

So if the lister accepts CountryCodes US, CA, and GB, and the UserAcceptedCountries includes US, and GB for this account, then I should only get CA back. Instead I am getting something like this:

CountryCode
CA,
CA,
GB,
US

Any help would be appreciated.

Thanks,
Steve

Edited by - blastrix on 01/11/2003 20:41:56

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-11 : 22:28:56


SELECT DISTINCT CountryCode
  FROM CountryCodes
       LEFT JOIN Users ON CountryCodes.CountryCode = Users.CountryCode
 WHERE Users.UserID IS NULL

-- or

SELECT CountryCode
  FROM CountryCodes
 WHERE NOT EXISTS(SELECT * FROM Users WHERE User.CountryCode = CountryCodes.CountryCode)

I think either should give you the direction to head...

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-01-11 : 23:28:02

Thanks, that first one did the trick. I understand why this one works, but I still don't get why it didn't do the same thing using the inequality operator?

Steve

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-12 : 05:01:04

UA.UserAccountID = UAC.UserAccountID AND LAC.CountryCode != UAC.CountryCode

for user 1 codes US,GB in both tables

you will get te entries for US - GB and GB - US.

Think of it as taking the cartesian product of the two tables then filtering on the conditions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources