I'm trying to find all rows in a table where a pair of values is not in
anther table: that is, I want to do something like this: SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y); which of course doesn't work. At the moment I've bodged around it by doing this: SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b FROM y); but this strikes me as really ugly. Can anyone a more elegant way to get what I want? TIA, -- John English |
Am 27. November 2016 11:13:33 MEZ, schrieb John English <[hidden email]>:
>I'm trying to find all rows in a table where a pair of values is not in > >anther table: that is, I want to do something like this: > > SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y); > >which of course doesn't work. > >At the moment I've bodged around it by doing this: > > SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b >FROM y); > >but this strikes me as really ugly. Can anyone a more elegant way to >get >what I want? > >TIA, Hi, Can this problem be solved by an correlating sub select?! Maybe something like this: Select * from x where not exists ( select 1 from y where x.a = y.a and x.b = y.b ) Or like your first SQL but convert to string and concatenate that would work but probably very slow as no index could be used. With kind regards Thomas |
In reply to this post by John English-3
Op 27-11-2016 om 11:13 schreef John English:
> I'm trying to find all rows in a table where a pair of values is not > in anther table: that is, I want to do something like this: > > SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y); > > which of course doesn't work. > > At the moment I've bodged around it by doing this: > > SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b > FROM y); > > but this strikes me as really ugly. Can anyone a more elegant way to > get what I want? > > TIA, Can't you use an ordinary join ? Something like Select x.* From x, y Where x.a = y.a And x.b = y.b Regards, Harm-Jan |
In reply to this post by John English-3
Hi John,
The outer join is the typical SQL approach to computing the difference between two relations: select * from x left join y on x.a = y.a and x.b = y.b where y.a is null; Hope this helps, -Rick On 11/27/16, 2:13 AM, John English wrote: > I'm trying to find all rows in a table where a pair of values is not > in anther table: that is, I want to do something like this: > > SELECT * FROM x WHERE (a,b) NOT IN (SELECT DISTINCT a,b FROM y); > > which of course doesn't work. > > At the moment I've bodged around it by doing this: > > SELECT * FROM x WHERE a||'-'||b NOT IN (SELECT DISTINCT a||'-'||b > FROM y); > > but this strikes me as really ugly. Can anyone a more elegant way to > get what I want? > > TIA, |
In reply to this post by Zorro
On 27/11/2016 17:36, Zorro wrote:
> Can't you use an ordinary join ? > > Something like > Select x.* From x, y Where x.a = y.a And x.b = y.b That would work if I wanted all (a,b) from X where (a,b) occurs in Y; unfortunately what I want is all (a,b) from X where (a,b) DOES NOT occur in Y. -- John English |
In reply to this post by Rick Hillegas-3
On 27/11/2016 18:29, Rick Hillegas wrote:
> Hi John, > > The outer join is the typical SQL approach to computing the difference > between two relations: > > select * from x left join y > on x.a = y.a and x.b = y.b > where y.a is null; Ah, excellent: very clever. This looks like the perfect solution! (And it also runs in a few seconds rather than many many minutes.) -- John English |
Free forum by Nabble | Edit this page |