select * from tab where not exists (select * from tab as tab2 where num1 = t1.num2);
As you see, there are many ways to obtain the result you want ... This is a direct translation of the English "I want to identify each row where num2 does not have a matching num1". The most efficient way to do this will depend on the characteristics of the data. For big tables Select * From tab Where num2 not in (select distinct num1 From tab); Will be the most efficient ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Paul Sanderson > Sent: Monday, 20 May, 2013 08:00 > 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