I think this does what you want. create table tab (num1 int unique,num2 int); insert into tab values(1,3); insert into tab values(2,3); insert into tab values(3,2); insert into tab values(4,1); insert into tab values(5,11); insert into tab values(6,3); insert into tab values(7,9);
sqlite> select t2.num1,t2.num2 from tab t2 where (select count(*) from tab t1 where t2.num2 == t1.num1) == 0; 5|11 7|9 If you don't want to see num2 in the query answer: sqlite> select num1 from (select t2.num1,t2.num2 from tab t2 where (select count(*) from tab t1 where t2.num2 == t1.num1) == 0); 5 7 -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Monday, May 20, 2013 7:00 AM To: General Discussion of SQLite Database Subject: [sqlite] query help I have a table of the form create table tab (num int1 unique, num2, int) for each row for num2 there is usually a matching num1. But not always. I want to identify each row where num2 does not have a matching num1 example data might be num1 num2 1 3 2 3 3 2 4 1 5 11 6 3 7 9 in this example my query would return rows 5 and 7 as there is no match on num1 for 11 and 9 Any ideas, cheers. _______________________________________________ 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
