Thanks a lot for that. Considering my configuration:
Table1: T1.id, t2.id, t1.name (, some other fields) Table2: T2.id, t3.id (m:n relation) Table3: T3.id, name, value, status (values: 0, 1, 9) I still got a problem with this one: select * from MyTable t1 join MyTable t2 on (t1.name = t2.name) where t1.status = 0 and t2.status = 1 and t1.value != t2.value; Unfortunately I forgot to mention, that there is a name field in T1 which contains different information as t3.name. This is the Information I have to select the Rest. Additionally the Status field is only part of T3. Sorry ... Ralf -----Ursprüngliche Nachricht----- Von: [email protected] [mailto:[email protected]] Im Auftrag von Igor Tandetnik Gesendet: Dienstag, 15. März 2011 19:48 An: [email protected] Cc: Ralf Jantschek Betreff: Re: [sqlite] Comparing Value of a Table and creating a Delta Report On 3/15/2011 2:29 PM, Ralf Jantschek wrote: > I have to compare entries with status=0 to status=1 and find out the > differences in name, value fields. > - are there name entries with Status=0 that are not there with > Status=1 select * from MyTable where status = 0 and name not in ( select name from MyTable where status = 1); > - are there name entries with Status=0 that have a different value > under Status=1 select * from MyTable t1 join MyTable t2 on (t1.name = t2.name) where t1.status = 0 and t2.status = 1 and t1.value != t2.value; > - are there name entries with Status=1 that are not there with > Status=0 select * from MyTable where status = 1 and name not in ( select name from MyTable where status = 0); -- Igor Tandetnik _______________________________________________ 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
