Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inner join syntax

Inner join syntax

From
"Chad Thompson"
Date:
I have a query:
select
 distinct on (l.full_phone) l.full_phone, l.id
from lists l inner join timezone tz on l.area_code = tz.area_code
inner join tiers t on phonenum_substr(l.full_phone) = t.npanxx
where l.client_id = 16
and l.list_of_lists_id = 170
and tz.greenwich = '-7'
and t.tier in ('A','B')
 
I am trying to add the following restriction to the above query.  And I'm lost some where.
 
where not exists (
select cr.phonenum
from call_results cr
where client_id = 16
and l.full_phone = cr.phonenum)
 
In case its not clear I want the first query but dont want those numbers that are in call_results
 
Any Ideas?
 
TIA
Chad

Re: Inner join syntax

From
"Josh Berkus"
Date:
Chad,

> select distinct on (l.full_phone) l.full_phone, l.id
> from lists l inner join timezone tz on l.area_code = tz.area_code
> inner join tiers t on phonenum_substr(l.full_phone) = t.npanxx
> where l.client_id = 16
> and l.list_of_lists_id = 170
> and tz.greenwich = '-7'
> and t.tier in ('A','B')
>
> I am trying to add the following restriction to the above query.  And
> I'm lost some where.
>
> where not exists (
> select cr.phonenum
> from call_results cr
> where client_id = 16
> and l.full_phone = cr.phonenum)

You're gonna feel silly ... you just need to replace the second "WHERE"
with an "AND", i.e. ...

AND NOT EXISTS (
SELECT cr.phonenum ...

-Josh Berkus

Re: Inner join syntax

From
"Chad Thompson"
Date:
Sometimes i cant see the trees for the forest. :-)

Thanks Josh, I was making it too complicated

Chad

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Friday, October 11, 2002 10:24 AM
Subject: Re: [NOVICE] Inner join syntax


> Chad,
>
> > select distinct on (l.full_phone) l.full_phone, l.id
> > from lists l inner join timezone tz on l.area_code = tz.area_code
> > inner join tiers t on phonenum_substr(l.full_phone) = t.npanxx
> > where l.client_id = 16
> > and l.list_of_lists_id = 170
> > and tz.greenwich = '-7'
> > and t.tier in ('A','B')
> >
> > I am trying to add the following restriction to the above query.  And
> > I'm lost some where.
> >
> > where not exists (
> > select cr.phonenum
> > from call_results cr
> > where client_id = 16
> > and l.full_phone = cr.phonenum)
>
> You're gonna feel silly ... you just need to replace the second "WHERE"
> with an "AND", i.e. ...
>
> AND NOT EXISTS (
> SELECT cr.phonenum ...
>
> -Josh Berkus
>
>
>