Mark,
What I have so far is: SELECT tbl1.email AS email1, tbl2.email AS email2 SELECT IF(tbl1.email IS NOT NULL, tbl1.email, tbl2.email) as email FROM tbl1 LEFT JOIN tbl2 USING (email)
.. which will get me all the addresses from tbl1, with the matching address from tbl2 in email2 or NULL if there is no match.
What I want is to also get all the addresses in tbl2, with NULL against email1 where there is no match. That is what I thought an OUTER join did but it seems I'm wrong.
An OUTER join can be a LEFT or RIGHT join like this:
SELECT tbl1.email AS email1, tbl2.email AS email2 FROM tbl1 LEFT OUTER JOIN email2 USING (email);
will show you all the records from the left table (in this case tbl1) and NULL for any records that didn't appear in the right table (tbl2).
Perhaps what you want is a union query:
SELECT DISTINCT email FROM tbl1 UNION SELECT DISTINCT email FROM tbl2 ORDER BY email;
This should give you one result for each distinct entry between the two tables, if I understand it correctly. Worked for my (admittedly very small) test db.
Hope this helps,
Jim
Jim Rippon wrote:
An OUTER join can be a LEFT or RIGHT join like this:
SELECT tbl1.email AS email1, tbl2.email AS email2 FROM tbl1 LEFT OUTER JOIN email2 USING (email);
will show you all the records from the left table (in this case tbl1) and NULL for any records that didn't appear in the right table (tbl2).
What I wanted was this plus also a NULL for any records that don't appear in tbl1: tbl1: a, b, c tbl2: b, c, d LEFT JOIN: (a,NULL), (b,b), (c,c) What I want: (a,NULL), (b,b), (c,c), (NULL,d)
(Not sure how clear that is!)
Perhaps what you want is a union query:
SELECT DISTINCT email FROM tbl1 UNION SELECT DISTINCT email FROM tbl2 ORDER BY email;
This should give you one result for each distinct entry between the two tables, if I understand it correctly. Worked for my (admittedly very small) test db.
Ah, that's almost perfect for what I want. I am now using: SELECT email FROM tbl1 UNION DISTINCT SELECT email FROM tbl2;
UNION DISTINCT ensures that identical records in both tables do not come out twice in the result set.
Thanks for your help!
-
Jim Rippon -
Mark Rogers