I believe this will work as you want:
select h1.*
FROM hist h1
LEFT JOIN hist h2
on h1.tel = h2.tel
and h2.date_h < '20041027'
where h1.date_h = '20041027'
and h2.tel is null;
This query should return all of the rows from the hist table where the tel
value appears for the date you supplied but no earlier. I would also try
these other variations to see if you get better or worse performance.
select h1.*
FROM hist h1
LEFT JOIN hist h2
on h2.tel = h1.tel
and h2.date_h < h1.date_h
where h1.date_h = '20041027'
and h2.tel is null;
select h1.*
FROM hist h1
LEFT JOIN hist h2
on h2.tel = h1.tel
and h2.date_h < h1.date_h
and h2.date_h < '20041027'
where h1.date_h = '20041027'
and h2.tel is null;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Hector Villafuerte <[EMAIL PROTECTED]> wrote on 10/27/2004
10:21:08 PM:
> Can I execute this query in a single JOIN statement?
>
> select * from hist
> where date_h = '20041027'
> and tel not in
> (select distinct tel from hist where date_h < '20041027')
>
> I know I could do it using a temporary table, but I wonder if
> there's a way to do it directly.
> Thanks!
> Hector
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
