By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Receiving duplicate values using not equal

P: 9
I am trying to get alll the results of everything in the north where the towns do not match the towns contained in a seperate table, everytime I run this I am getting 37 duplications of each record in the result.

SELECT tbMainData2.ID, tbMainData2.URN, tbMainData2.ContactName, tbMainData2.RestaurantName, tbMainData2.Add1, tbMainData2.Add2, tbMainData2.Add3, tbMainData2.town, tbMainData2.county, tbMainData2.postcode, tbMainData2.Telephone, tbMainData2.Contact_Ok, tbMainData2.region, tbMainData2.RestaurantorTakeaway, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE tbmaindata2.region='NORTH' And (((tbMainData2.town)<>northgeo.field2))
Jan 20 '10 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbMainData2 
  2. WHERE region='NORTH' 
  3. AND 
  4. town NOT IN (SELECT field2 FROM northgeo)
Jan 20 '10 #2

P: 9
Thanks Chip, I have now discovered where I went wrong, it was my syntax, I should have used the following

SELECT DISTINCT tbMainData2.ID, tbMainData2.URN, tbMainData2.ContactName, tbMainData2.RestaurantName, tbMainData2.Add1, tbMainData2.Add2, tbMainData2.Add3, tbMainData2.town, tbMainData2.county, tbMainData2.postcode, tbMainData2.Telephone, tbMainData2.Contact_Ok, tbMainData2.region, tbMainData2.RestaurantorTakeaway, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE (((tbMainData2.region)='NORTH' & ((([tbMainData2].[town])<>[northgeo].[field2]))));
Jan 20 '10 #3

Expert 100+
P: 1,287
The only difference I see between this and the previous SQL you posted are a bunch of unnecessary parentheses and brackets, and using & instead of AND. What do you think was the problem with your syntax?
I would recommend consistent capitalization for clarity in your own project, as well as when posting for others.
Jan 20 '10 #4

P: 9
I changed SELECT to SELECT DISTINCT and yes I changed AND to &. No need for your snotty answer at all thanks very much!!!!, So no more unnecessary replies thanks. sorry for the unnecessary brackets that Access puts in itself when the query is saved
Jan 21 '10 #5

nico5038
Expert 2.5K+
P: 3,072
Dear djdaveg,

I'm a bit confused why you're using this type of language. I hope you do understand that the experts here are investing their spare free time to help others to solve there problems, so some respect is needed. A wrong attitude can lead to experts ignoring your posts.

Well the problem solution you found is a bit odd. The change of the AND into an & tells me that field [northgeo].[field2] consists of the text "NORTH" concatenated with a True or False depending on the town name comparison, something certainly against normalization rules.
The need for a DISTINCT clause tells me that the JOIN of the tables isn't done on a unique field, thus resulting in a Cartesian product.

In a well design data model the Town would have a unique ID and filtering the town table on the field Region should make the solution from ChipR work.
I do see however that you have the Region in the tbMainData2 table and that's also against normalization rules. You run the risk that the same town is recorded in two regions.....

Regards

Nic;o)
Jan 22 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.