The customer's requirements has gotten worse and these queries are getting very ugly, so I think I should either:
1) do a select into a temp table and then run a bunch of queries to remove records based on another query (this might require substantial code changes) Or 2) use the 'except' clause in query. My questions are: 1) with option one, would the statement be "Delete * from table_name where orderno in (Select orderno from table_name where name = 'something')" 2) on option two, is there a way to except just certain records by a key or does the entire record have to match? Like can I say Select * from table_name except Select orderno from table_name where name = 'something' except Select orderno from table_name where name = 'something else' 2.a) does the second except apply to the original query? Thank you for your guidance, Gene -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Wednesday, July 29, 2009 9:33 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] except and select 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<g...@bystorm.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
