0

Hello...

I need to get data from one table while omitting data that exist in the other table. This is the query I am running but I keep on getting duplicates since there are more the one row in the second table.

Table1 - 'countries' with fields 'Country_Code' and 'Country_Name'
Table2 - 'allow' with field 'Country_Code'

SELECT countries.Country_Code, Country_Name FROM countries, allow WHERE countries.Country_Code != allow.Country_Code ORDER BY Country_Name

Since there are multiple rows in table2(allow) I get duplicate results.

Any help would be appreciated.

2
Contributors
9
Replies
10
Views
8 Years
Discussion Span
Last Post by jbisono
0

try this. SELECT DISTINCT countries.Country_Code, Country_Name FROM countries, allow WHERE countries.Country_Code != allow.Country_Code ORDER BY Country_Name Regards.

0

Just tried that,

It removes the duplicate but, it selects all the records from countries database and does not check allow database for existing records.

Thanks For the Response...

0

SELECT DISTINCT countries.Country_Code, Country_Name FROM countries INNER JOIN allow ON countries.Country_Code != allow.Country_Code ORDER BY Country_Name

What about that.

0

Still an issue. it still gets records which it should not for != Compare.

I could try to use a different query to check if the Country Code exist in the second table but if I put it in a loop it will do something like 400 queries which is not good for the DB. Really trying to avoid that.

0

Ok, I think I am a little lost lets put some data into that table
COUNTRIES
USA - UNITED STATES
DR - DOMINICAN REPUBLIC
MX - MEXICO
PR - PUERTO RICO

ALLOW
DR
MX

So what you are saying is that in ALLOW table you can have multiple DR and MX?

0

That is exactly how my tables are set up. the problem is that I want to get the Country Name along with their Country Code, but I do not want that information for the countries which are in the allow table. in this case DR and MX.

With that table data above, I would run the query to get only these records:
RESULTS
USA - UNITED STATES
PR - PUERTO RICO

1

SELECT country_code, countries
FROM countries
WHERE (country_code NOT IN
(SELECT CountryCode
FROM allow))

ok so your statement should be like this.
regards.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.