Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL

SQL

From
Randy Jonasz
Date:
Hi everyone,

I have a quick question. If anyone could help me that would be great.  I
have two tables in postgres.

table a
banid int4 primary key,
flags varchar(64)


table b
banid int4 foreign key
validate int4


What SQL statement would allow me to search for records in table a which
are not in table b.  The additional constraint is that I only want records
from table a which do not have 'v' in the flags column.

Here's an example SQL statement I've been using but I'm thinking it is
flawed.

SELECT distinct a.banid FROM a,b WHERE a.banid != b.banid AND a.flags NOT
LIKE '%v%';

Any help would be most welcome!

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle

RE: SQL

From
"Nick Fankhauser"
Date:
how about:

SELECT banid FROM a WHERE flags NOT LIKE '%v%' AND banid NOT IN (SELECT
banid FROM b);

-Nick

----------------------------------------------------------------------------
-----
Nick Fankhauser

Business: nickf@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
          Ray Ontko & Co.   Software Consulting Services
http://www.ontko.com/

Personal: nickf@infocom.com http://www.infocom.com/~nickf

RE: SQL

From
Randy Jonasz
Date:
That looks better!  Thanks for your help

Cheers,

Randy

On Wed, 8 Nov 2000, Nick Fankhauser wrote:

>
> how about:
>
> SELECT banid FROM a WHERE flags NOT LIKE '%v%' AND banid NOT IN (SELECT
> banid FROM b);
>
> -Nick
>
> ----------------------------------------------------------------------------
> -----
> Nick Fankhauser
>
> Business: nickf@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
>           Ray Ontko & Co.   Software Consulting Services
> http://www.ontko.com/
>
> Personal: nickf@infocom.com http://www.infocom.com/~nickf
>
>
>

Randy Jonasz
Software Engineer
Click2net Inc.
Web:  http://www.click2net.com
Phone: (905) 271-3550

"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle

Re: SQL

From
Andrew Gould
Date:
I think you need to use a LEFT JOIN in your SQL
statement.  I'm not sure how well PostgreSQL supports
JOINs, but the following might work:

select tablea.banid, tablea.flags, tableb.banid from
tablea left join tableb on tablea.banid = tableb.banid
where tableb.banid = null and tablea.flags not like
'%v%';


--- Randy Jonasz <rjonasz@click2net.com> wrote:
>
> Hi everyone,
>
> I have a quick question. If anyone could help me
> that would be great.  I
> have two tables in postgres.
>
> table a
> banid int4 primary key,
> flags varchar(64)
>
>
> table b
> banid int4 foreign key
> validate int4
>
>
> What SQL statement would allow me to search for
> records in table a which
> are not in table b.  The additional constraint is
> that I only want records
> from table a which do not have 'v' in the flags
> column.
>
> Here's an example SQL statement I've been using but
> I'm thinking it is
> flawed.
>
> SELECT distinct a.banid FROM a,b WHERE a.banid !=
> b.banid AND a.flags NOT
> LIKE '%v%';
>
> Any help would be most welcome!
>
> Randy Jonasz
> Software Engineer
> Click2net Inc.
> Web:  http://www.click2net.com
> Phone: (905) 271-3550
>
> "You cannot possibly pay a philosopher what he's
> worth,
> but try your best" -- Aristotle
>


__________________________________________________
Do You Yahoo!?
Thousands of Stores.  Millions of Products.  All in one Place.
http://shopping.yahoo.com/