asked on

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

Vitor Montalvão

I can't understand your question.
Please explain with sample data.

Ryan Chong

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

Amour22015

ASKER

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

Sean Stuber

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

Amour22015

ASKER

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

Vitor Montalvão

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

Amour22015

ASKER

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

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Vitor Montalvão,

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

Thanks

Vitor Montalvão

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

Amour22015

ASKER

This is what I am looking for thanks