* 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
