[ALUG] MySQL Joins
mark at quarella.co.uk
Wed Jun 3 14:15:02 BST 2009
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
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!
Mark Rogers // More Solutions Ltd (Peterborough Office) // 0845 45 89 555
Registered in England (0456 0902) at 13 Clarke Rd, Milton Keynes, MK1 1LG
More information about the main