Hello
On Thu, Apr 25, 2002 at 03:29:34AM +0100, [EMAIL PROTECTED] wrote:
> > Guten Tag Donna Robinson,
> Hi 2u2 sir!
> and god bless your cotton socks.
>
> > i hope this will help you..
> ...
> it didn't but it gave me enough of a clue so that this did:
> select dancedetails.coupletypes.*
> from dancedetails.coupletypes
> left join dancedata2.couples
> on dancedetails.coupletypes.name=dancedata2.couples.name
> where dancedata2.couples.name is null;
>
> So tell me - why does a left join work where an inner join refused to?
> (have only been sql-ing for about 2 wks)
First, you might be served better by reading a good tutorial or book
(see the manual for suggestions), than to ask for basic principles of
SQL on the mailing list.
It may become more obvious, if you try the query without WHERE clause
at all:
SELECT ddetails.name, ddata.name
FROM dancedetails.coupletypes AS ddetails,
dancedata2.couples AS ddata
This will result in a Cartesian product (cross product) of the two
specified tables, i.e. every field of the "left" table is paired with
every field of the "right" table.
Now, if you add something like "WHERE ddetails.name = ddata.name" you
get only the rows from that set, which fullfil the condition, that
their names are equal.
If you try "WHERE ddetails.name != ddata.name", you get all rows for
which the names in the pair are different. So you got exactly what you
asked for, you see?
But from your earlier explanations you didn't want to ask "in which
rows are the names different", but "which name exists only in one of
the tables".
A LEFT JOIN will give all rows like a normal join (the ","), but also
all rows of the left table, which have no equivalent in the right one,
with the values for the right one filled with NULL values.
Therefore "WHERE dancedata2.couples.name IS NULL" will give you only
those latter mentioned rows of the result set, which have no pair
equivalent value in the right table.
This is probably still not what you want: It won't give you values,
which are only in the right table, but not in the left one. So a LEFT
JOIN is only the correct solution for your question, if you know
beforehand, that only the left table has "orphaned" values.
What you need is a "FULL OUTER JOIN". Unfortunately this is not
supported yet by MySQL. So you would need to run two queries, a LEFT
JOIN and a RIGHT JOIN (or a LEFT JOIN with tables swapped) to get all
results.
Regards,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
