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
