Many thanks
I have checked out the mysql site and am starting to get my head around
it. How would I modify your query so that I had a clause that selected
in another value from another table?
Your suggestions have been most helpful
Lets say I had another table called others
And I wanted the query you supplied to INCLUDE items from others that
are not in test
Eg, (old example)
Others
Name item
----------
Abc 10
Def 11
Geh 12
Ijk 13
Lmn 99
I would like to be able to have the 99 added to the selection output of
the previous query!
select t1.*
from test t1
left join test t2 on
t2.id=2 and
t2.item=t1.item
where t2.id is NULL; (AND ADD VALUES FROM OTHERS.ITEM WHERE
OTHERS.ITEM IS NOT IN TEST.ITEM
Best regards
Thanks again
Scott
-----Original Message-----
From: Roger Baklund [mailto:roger@;charlott.no]
Sent: 31 October 2002 11:44
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: A simple one that gets me!
* scott
> Can you explain the query if you have time!
[...]
> select t1.*
> from test t1
> left join test t2 on
> t2.id=2 and
> t2.item=t1.item
> where t2.id is NULL;
This is a self join. Read one table, left join to the same table looking
for the records you do _not_ want, and then in the WHERE clause check
for NULL in the left-joined table.
Note that it is necessary to use aliases for the table name in this
case.
Also, note that the match criteria for the join is placed in the ON
clause, not in the WHERE clause, while the match criteria for the final
result ("t2.id is NULL", wich means we did not find a t2 row) is in the
WHERE clause.
<URL: http://www.mysql.com/doc/en/SELECT.html >
<URL: http://www.mysql.com/doc/en/JOIN.html >
--
Roger
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
