943,579 Members | Top Members by Rank

Web Development > Databases > MySQL > Getting data from multiple table issue
Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 1004
  • MySQL RSS
Permalink
Jun 21st, 2009
0

Getting data from multiple table issue

Expand Post »
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'

MySQL Syntax (Toggle Plain Text)
  1. 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.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
phpdude89 is offline Offline
11 posts
since Nov 2008
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

try this.

SELECT DISTINCT countries.Country_Code, Country_Name FROM countries, allow WHERE countries.Country_Code != allow.Country_Code ORDER BY Country_Name
Regards.
Last edited by jbisono; Jun 22nd, 2009 at 9:14 am.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
431 posts
since May 2009
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

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...
Reputation Points: 10
Solved Threads: 0
Newbie Poster
phpdude89 is offline Offline
11 posts
since Nov 2008
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

Quote ...
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.
Last edited by jbisono; Jun 22nd, 2009 at 11:24 am.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
431 posts
since May 2009
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
phpdude89 is offline Offline
11 posts
since Nov 2008
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

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?
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
431 posts
since May 2009
Permalink
Jun 22nd, 2009
0

Re: Getting data from multiple table issue

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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
phpdude89 is offline Offline
11 posts
since Nov 2008
Permalink
Jun 23rd, 2009
1

Re: Getting data from multiple table issue

Quote ...
SELECT country_code, countries
FROM countries
WHERE (country_code NOT IN
(SELECT CountryCode
FROM allow))
ok so your statement should be like this.
regards.
Last edited by jbisono; Jun 23rd, 2009 at 9:19 am.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
431 posts
since May 2009
Permalink
Jun 23rd, 2009
0

Re: Getting data from multiple table issue

Thanks A lot.

That SQL Query works perfectly.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
phpdude89 is offline Offline
11 posts
since Nov 2008
Permalink
Jun 23rd, 2009
0

Re: Getting data from multiple table issue

Glad to know that, take care.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
431 posts
since May 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: compare 2 columns with wildcard
Next Thread in MySQL Forum Timeline: SQL problem





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC