[Resolved] PLEASE HELP! Need one field from 2 databases

paulg

Hi Guys,

I have 2 databases that I need to extract email addresses from.

What I want to do is end up with a result set containing :-

a) The 'email' field from DB1.members and DB2.users

b) The email addresses that are in DB1.members BUT NOT in DB2.users

c) The email addresses that are NOT in DB1.members but ARE in DB2.users.

Confused? .. I am... 😕

There is NO field by which to compare the databases/tables as in a LEFT JOIN ... ON, or maybe the syntax has me confused.

Any help please??????

steadyguy

Those three conditions work out to all the email addresses in both dbs.
SELECT a.email, b.email
FROM DB1.members a, DB2.users b;

That's not a very useful result set - if you want to know which ones are in both dbs and which ones aren't ... so:
(emails in common)
SELECT a.email, b.email
FROM DB1.members a, DB2.users b
WHERE a.email = b.email;

and
(emails NOT in common)
SELECT a.email, b.email
FROM DB1.members a, DB2.users b
WHERE a.email != b.email;

paulg

Thanks steadyguy

But this causes a problem.

My DB1.members contains 51 records and DB2.users contains 119 records.

SELECT a.email, b.email FROM DB1.members a, DB2.users b;

This produces a result set of 6069 records (which is 51 x 119)!

Using WHERE a.email = b.email DOES work (13 results) but the
a.email != b.email gives me 6056 (6069 - 13)😕

Aaarrggggh!

Paul.

steadyguy

No worries ... the first query was just an example of a 'not very useful' result set.

And about the Third one not working ... That's my fault, I should have realized that.

SELECT DISCTINCT(a.email), DISTINCT(b.email)
FROM DB1.members a, DB2.users b
WHERE a.email != b.email

That should work, but let me know how it goes.

drawmack

Originally posted by steadyguy
SELECT DISCTINCT(a.email), DISTINCT(b.email)
FROM DB1.members a, DB2.users b
WHERE a.email != b.email

This won't work either, someone needs to brush up on their predicate algebra.

This will return count(distinct(a.email)) * count(distinct(b.email) what you need is two selects like this

$aryEmails = array();
$sql = "select distinct(email) from members"
$query = mysql_query($sql);

for($i=0;$i<mysql_numrows($query);$i++) {
  array_push($aryEmails,mysql_result($query,$i,0));
} //end for

mysql_free_result($query);

//switch to other database code ommitted

$sql = "select distinct(email) from users where email not in ('";
$sql .=  implode("','",$aryEmails) . "')";
$query = mysql_query($sql);

for($i=0;$i<mysql_numrows($query);$i++) {
  array_push($aryEmails,mysql_result($query,$i,0));
} //end for

mysql_free_result($query);

print_r($aryEmails);

steadyguy

drawmack ... you're right ... that query won't work ... but it won't give count(distinct(a.email)) * count(distinct(b.email) ... it'll generate an error, and even when you fix the error, it'll never return a COUNT(). Should brush up on their SQL syntax? 😉

Also the solution you gave is incomplete; while it gives all emails from members, it only gives emails from users that are not in members - which could be done more easily by a simple LEFT JOIN query and checking for NULL values. But paulg said he also wants emails from users which are not in users ...

However ...

paulg, drawmack is right, you'll have to use two queries.

I personally think that the most efficient way to do it is with two JOIN queries.

SELECT a.email AS userEmail, b.email as memberEmail
FROM users.email a LEFT JOIN DB2.email b
ON a.email = b.email;

This query will return a result set with some NULL values in the second column (memberEmail) - these are the rows that are in users, but not in members. To find the ones that are in members, but not in users, replace the LEFT JOIN with a RIGHT JOIN (this is easier than switching table.columns).
If you want all three pieces of data on one page, you'll either need to run one query, then use mysql_free_results() as drawmack pointed out, in which case you can use the same result identifier (ie., $query = mysql_query($sql); ) then run the other, or you could also just use two result sets:

$query1 = 'SELECT a.email AS userEmail, b.email AS memberEmail FROM users.email a LEFT JOIN DB2.email b ON a.email = b.email';
$query2 = 'SELECT a.email AS userEmail, b.email AS memberEmail FROM users.email a RIGHT JOIN DB2.email b ON a.email = b.email';
$result1 = msyql_query($query1);
$result2 = mysql_query($query2);

paulg

Thanx to all who helped here.

steadyguy, you are a GOD! - well .. at least you solved my headache!😃

Cheers,
Paul

drawmack

Originally posted by steadyguy
drawmack ... you're right ... that query won't work ... but it won't give count(distinct(a.email)) * count(distinct(b.email) ... it'll generate an error, and even when you fix the error, it'll never return a COUNT(). Should brush up on their SQL syntax? 😉

I was giving the number of emails that it would return and using pseudo sql for that instead of saying this:

It'll return x * y emails where
x = the number of distinct email addresses in a
y = the number of distinct email addresses in b

Next time I will remember that you do not understand the premises and ideas behind the simplicity of using pseudo code and will explain myself fully.😃 😃