Maybe this way:
select t.*
from table_name t
left outer join table_name t_del
on t_del.operation = 'Deleted'
and t_del.Filename = t.RenameTo
where t.operation = 'Renamed'
and t_del.ID is null
union all
select t.*
from table_name t
left outer join table_name t_ren
on t_ren.operation = 'Renamed'
and t_ren.RenameTo = t.Filename
where t.operation = 'Deleted'
and t_ren.ID is null
Pavel
On Wed, Jul 29, 2009 at 10:17 AM, Gene Allen<[email protected]> wrote:
> I need to come up with a query where I can remove records that match a
> certain condition.
>
>
>
> I have file auditing data and I need the query to show all the files that
> were deleted and the files that were renamed but not the files that was
> deleted AND renamed TO. Problem is the Delete is one record and the Rename
> is another one.
>
>
>
> For example, in the sample table below, I want records 3 and 4, since A was
> deleted (record 1) AND renamed to (record 2)
>
>
>
> ID Filename operation RenamedTo
>
> 1 A Deleted
>
> 2 B Renamed A
>
> 3 C Renamed D
>
> 4 E Deleted
>
>
>
> I tried to use an except, but that won't work since the data won't match up
> between the records. Record 1 and 2 don't match.
>
>
>
> Due to a limitation in my program, I have to do this in a single select
> statement.
>
>
>
> Any advice would be wonderful!
>
>
>
> Thanks,
>
>
>
> Gene
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
