List:General Discussion« Previous MessageNext Message »
From:scott Date:October 31 2002 4:29pm
Subject:: A simple one that gets me!
View as plain text  
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@stripped] 
Sent: 31 October 2002 11:44
To: mysql@stripped
Cc: scott@stripped
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 <mysql-thread123618@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-scott=broadcastwarehouse.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
: A simple one that gets me!scott31 Oct
  • Re: A simple one that gets me!Roger Baklund31 Oct
    • mysql fills the disk with temporary files?Lars Andersson31 Oct
      • Re: mysql fills the disk with temporary files?gerald_clark31 Oct
        • Re: mysql fills the disk with temporary files?Lars Andersson31 Oct
RE: mysql fills the disk with temporary files?Kelly W [PCS] Black31 Oct
  • RE: mysql fills the disk with temporary files?Lars Andersson31 Oct
RE: mysql fills the disk with temporary files?Kelly W [PCS] Black31 Oct
  • RE: mysql fills the disk with temporary files?Lars Andersson31 Oct
RE: mysql fills the disk with temporary files?Jan Steinman1 Nov
  • RE: mysql fills the disk with temporary files?Lars Andersson1 Nov
RE: mysql fills the disk with temporary files?Kelly W [PCS] Black1 Nov