SQL help needed

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL help needed

John English-3
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL help needed

Thomas Meyer
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL help needed

Zorro
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL help needed

Rick Hillegas-3
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,

Reply | Threaded
Open this post in threaded view
|

Re: SQL help needed

John English-3
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL help needed

John English-3
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