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