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 <mos99@stripped> 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 <mos99@stripped> 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/mysql?unsub=1
> > >
> > >
> >
> >
> >
> >
> >__________________________________
> >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/mysql?unsub=1
>
>
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
