The passage you quoted is so vague that it should be removed or rewritten. The type of query that the OP wants to write is more than likely one of those "exceptions" discussed as an afterthought. That makes your warning inaccurate, doesn't it.
There are many valid reasons for putting conditions into the ON clause of a JOIN, especially when using the outer JOIN forms. In fact, some queries require several conditions in the ON clause in order to perform correctly. There have been several good generic suggestions so far but everyone has had to guess what is going on because the OP failed to post their table structures. Given more information about the actual data, a more precise answer can be provided. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Turner <[EMAIL PROTECTED]> wrote on 11/09/2005 01:49:01 PM: > It appears mysql recommends against the solution given > to you > > http://dev.mysql.com/doc/refman/5.0/en/join.html > > You should generally not have any conditions in the > ON part that are used to restrict which rows you want > in the result set, but rather specify these conditions > in the WHERE clause. There are exceptions to this > rule. > > If you could send a sample of table1, table2, and the > result set you want to arrive at I'd appreciate it. > > Dave > > > --- mos <[EMAIL PROTECTED]> wrote: > > > At 09:54 PM 11/8/2005, David Turner wrote: > > >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 > > > > > > Hi Dave, > > Another user privately e-mailed me the > > errors of my ways.<g> > > > > Here is the problem (returns 0 rows): > > > > select * from table1 t1 left join table2 t2 on > > > > t1.date1=t2.date2 where > > > > t2.date2 is null > > > > where t2.name='Smith' > > > > Here is the solution: > > > > select * from table1 t1 left join table2 t2 on > > t1.date1 = t2.date2 AND > > t2.name = 'Smith' WHERE t2.date2 is null; > > > > I had to move another reference of t2 from the Where > > clause to the Join, > > namely t2.name='Smith' gets moved to the join. I > > thought I had done this > > before but I left one reference in the Where clause > > that prevented any rows > > from being returned.It appears the conditional in > > the join clause gets > > executed before the rows are joined, and the Where > > clause gets executed > > after the join. > > > > Mike > > > > > > >--- 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 > > > >
