Getting data from multiple table issue

[phpdude89]

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.

[jbisono]

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

[phpdude89]

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...

[jbisono]

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.

[phpdude89]

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.

[jbisono]

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?

[phpdude89]

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

[jbisono]

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

ok so your statement should be like this.
regards.

[phpdude89]

Thanks A lot.

That SQL Query works perfectly.

[jbisono]

Glad to know that, take care.