TSQL - Why is this query not working?

Hi Experts,

I have this query:
Select Country
From tblNAsAddresses A Join cnv_AddressXrefCountry B
On A.Country <> B.NAsName
Group By Country
Order By Country

Open in new window


Yet still the country display even when I know that the country is in the:
cnv_AddressXrefCountry B Table?

Thanks for helping
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't understand your question.
Please explain with sample data.
0
Ryan ChongCommented:
try use left join instead if you would only like to display the record in tblNAsAddresses A, which its country not found in cnv_AddressXrefCountry B:

Select Country
From tblNAsAddresses A left Join cnv_AddressXrefCountry B
On A.Country = B.NAsName
where B.NAsName is null
Group By Country
Order By Country
0
Amour22015Author Commented:
Ok,

Using the query giving I get
Example:
BANGLEDESH

Yet in the:
cnv_AddressXrefCountry table I have:
BANGLEDESH
And
tblNAsAddresses table I have:
BANGLEDESH

So:
A.Country = BANGLEDESH
and
B.NAsName = BANGLEDESH
So:
On A.Country <> B.NAsName
should BANGLEDESH not show up?

A.Country is a Country column/field
B.NAsName is a country column/field
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
If either table has another row with a value of 'USA'  (or any other value that is not BANGLEDESH)  then YES,  BANGLEDESH should show up.


because your join is based on <>  so  any row in one table that matches that condition with any row of the other table will be returned.

Since USA <> BANGLEDESH,    then BANGLEDESH will be returned
0
Amour22015Author Commented:
I should add that:
cnv_AddressXrefCountry
is a cross reference table to make sure anything in
tblNAsAddresses.country is in the cross reference table.  So If a user inputs example:
XX in the country field of tblNAsAddress.country.

Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't use not equal in JOIN. Try this:
Select Country
From tblNAsAddresses A 
Where Not Exists (SELECT 1 FROM cnv_AddressXrefCountry B WHERE A.Country = B.NAsName)
Order By Country

Open in new window

0
Amour22015Author Commented:
It is mentioned:
Since USA <> BANGLEDESH,    then BANGLEDESH will be returned

Then what I am looking for is:

What if:
BANGLEDESH <> BANGLEDESH, then should not BANGLEDESH show up?

This is what I am saying?

Thanks
0
Deepak ChauhanSQL Server DBACommented:
Select Country
From tblNAsAddresses
where Country not in (select NAsName from cnv_AddressXrefCountry)
Group By Country
Order By Country

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Vitor Montalvão,

I tried your query but I get a lot of NUll's and I don't want that.

Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then add a filter to do not show NULL :)
Select Country
From tblNAsAddresses A 
Where Not Exists (SELECT 1 FROM cnv_AddressXrefCountry B WHERE A.Country = B.NAsName)
  And A.Country IS NOT NULL
Order By Country

Open in new window

0
Amour22015Author Commented:
This is what I am looking for thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.