* 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 --------------------------------------------------------------------- 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
