List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:October 31 2002 4:58pm
Subject:Re: A simple one that gets me!
View as plain text  
* scott
[...]
> 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

This table has a different strucure... there was no 'Name' in the 'test'
table, and there is no 'id' in 'other' table. The only way these tables
could be joined is by the 'item' field, and you would have to somehow create
an 'id' for these rows.

> 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

With mysql 4.0.x you could use UNION:

<URL: http://www.mysql.com/doc/en/UNION.html >

With mysql 3.23.x you can use a temporary table:

create temporary table tmp1
  select t1.*
     from test t1
     left join test t2 on
       t2.id=2 and
       t2.item=t1.item
     where t2.id is NULL;

insert into tmp1
  select 9999,t1.item    # 'id' is set to 9999 for any rows found in 'other'
     from other t1
     left join test t2 on
#       t2.id=2 and
       t2.item=t1.item
     where t2.id is NULL;

select * from tmp1;

(You don't want to exclude those with id=2 only, but _any_ item found in
test, right? That's why I removed the test for t2.id=2)

HTH,

--
Roger

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