If you could present sample data of both table1, table2, and an example of the result set it would be easier to give you the sql. I believe you could eliminate the temporary table with a subselect in the original query. The subselect is where you would specify 'Smith'.
Dave --- mos <[EMAIL PROTECTED]> wrote: > I would like to find the missing subset of rows in > table2 based on the rows > in table1. > > Normally it would look like this: > > select * from table1 t1 left join table2 t2 on > t1.date1=t2.date2 where > t2.date2 is null > > Well this works fine except I only want to compare a > subset of rows in > table2 for a particular person. > > I tried: > > select * from table1 t1 left join table2 t2 on > t1.date1=t2.date2 where > t2.date2 is null > where t2.name='Smith' > > and of course this doesn't work because the t2 row > can't be missing if it > finds 'Smith'. (I've created a Paradox-bad pun I > know<g>) > > The only solution I've found is to create a > temporary table with the rows > from t2 that belong to 'Smith', then run the left > join on the temporary table. > > I'm wondering if there is a better way that doesn't > involve temporary tables? > > TIA > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
