CODEWITHSUNDEEP
sqlsql-server-2008
EP2012
EP2012

Reputation: 341

Query to output unmatched column names

I have a table with two of the columns lets say Country, Country Code. The table can have multiple rows with same country and code. But country and code always have to match. How can I write a query that will find me a list of all rows where country and country code do not match.

If this is the table, I want the query to return row#4.. where Canada does not match with XYZ (it should have been CN).. There is a master list of country and codes in a different table lets say.. tblCountries.

enter image description here

Upvotes: 2

Views: 584

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

You can either use the select statement with the NOT EXISTS operator as suggested by Aaron Bertrand in the comments, or you can use a left join:

SELECT T.*
FROM MyTable T
LEFT JOIN TblCountries C ON(T.Country = C.Country AND T.CountryCode = C.CountryCode)
WHERE C.CountryId IS NULL -- Assuming you have a column by that name that's not nullable.

Giorgi Nakeuri's answer will also get you the result you are looking for, however, if there is a record in your table that have a country and a code that doesn't even exist in tblCountry then Aaron's answer or mine will return it, but Giorgi's will not.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

First of all if there is another table with contry and code then you are breaking normalization principles having same columns in another table.

You can do it like:

select * from testTable tt
join Countries c on ((c.Country = tt.Country and c.Code <> tt.Code) 
                      or (c.Code = tt.Code and c.Country <> tt.Country))

Upvotes: 2

Related Questions