[ALUG] MySQL Joins

Mark Rogers 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
    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!

-- 
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 mailing list