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.

Re: Getting data from multiple table issue 80 80

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

Re: Getting data from multiple table issue 80 80

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

Re: Getting data from multiple table issue 80 80

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.

Re: Getting data from multiple table issue 80 80

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.

Re: Getting data from multiple table issue 80 80

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?

Re: Getting data from multiple table issue 80 80

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

Re: Getting data from multiple table issue 80 80

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

ok so your statement should be like this.
regards.

Re: Getting data from multiple table issue 80 80

Thanks A lot.

That SQL Query works perfectly.

Re: Getting data from multiple table issue 80 80

Glad to know that, take care.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.