Skip site navigation (1) Skip section navigation (2)

Re: where clauses with and

From: "Rob" <rob(at)jamwarehouse(dot)com>
To: "Chad Thompson" <chad(at)weblinkservices(dot)com>,<paul(at)entropia(dot)co(dot)uk>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: where clauses with and
Date: 2002-10-17 15:33:14
Message-ID: MIENLCBGIJDKLHHBLCDGEEBMCCAA.rob@jamwarehouse.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Where usertype IN ('Drug
> Addiction','Alcohol Addiction')

will give you organizations who have either a usertype 'Drug Addiction' or
usertype 'Alcohol Addiction' whereas
using the inner join will give you organizations that have both


---
Rob

**************************
Rob Cherry
mailto:rob(at)jamwarehouse(dot)com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


> -----Original Message-----
> From: Chad Thompson [mailto:chad(at)weblinkservices(dot)com]
> Sent: 17 October 2002 05:22
> To: Rob; paul(at)entropia(dot)co(dot)uk; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] where clauses with and
>
>
> Help me out, maybe Im confused.
> Using AND means that the usertype field must contain both 'Drug Addiction'
> AND 'Alcohol Addiction' in each row, which is why it returned 0 rows.
> Using OR or IN would return the proper results.  Where usertype IN ('Drug
> Addiction','Alcohol Addiction')
> Is there an avantage to joining the table to itself?
>
> Thanks
> Chad
> ----- Original Message -----
> From: "Rob" <rob(at)jamwarehouse(dot)com>
> To: <paul(at)entropia(dot)co(dot)uk>; <pgsql-novice(at)postgresql(dot)org>
> Sent: Thursday, October 17, 2002 6:51 AM
> Subject: Re: [NOVICE] where clauses with and
>
>
> > A simple AND won't work in this case.  What you'll have to use
> is an inner
> > join.  Something like
> >
> > SELECT orgname
> > FROM users AS U1 INNER JOIN users AS U2 ON U1.orgname = U2.orgname
> > WHERE U1.usertype='Drug Addiction' AND U2.userType='Alcohol Addiction'
> >
> > That should do it :-)
> >
> > ---
> > Rob
> >
> > **************************
> > Rob Cherry
> > mailto:rob(at)jamwarehouse(dot)com
> > +27 21 447 7440
> > Jam Warehouse RSA
> > Smart Business Innovation
> > http://www.jamwarehouse.com
> > **************************
> >
> >
> > > -----Original Message-----
> > > From: pgsql-novice-owner(at)postgresql(dot)org
> > > [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of paul butler
> > > Sent: 17 October 2002 02:43
> > > To: pgsql-novice(at)postgresql(dot)org
> > > Subject: [NOVICE] where clauses with and
> > >
> > >
> > > Here's an odd one:
> > > I can't seem to make a where clause containing 'and' work
> > >
> > > db=# select orgname,usertype from users where usertype='Drug
> > > Addiction';
> > >               orgname               |    usertype
> > > ------------------------------------+----------------
> > >  Turning Point Scotland             | Drug Addiction
> > >  Govan Youth Access Project         | Drug Addiction
> > >  Community Learning West Team       | Drug Addiction
> > >  Possil Learning Centre             | Drug Addiction
> > >  Langside College (Glenwood Campus) | Drug Addiction
> > >  Rehab Remanufacturing Services Ltd | Drug Addiction
> > >  Greater Pollok Addiction Service   | Drug Addiction
> > >
> > > db=# select orgname,usertype from users where usertype='Alcohol
> > > Addiction';
> > >               orgname               |     usertype
> > > ------------------------------------+-------------------
> > >  Possil Learning Centre             | Alcohol Addiction
> > >  Greater Pollok Age Concern         | Alcohol Addiction
> > >  Rehab Remanufacturing Services Ltd | Alcohol Addiction
> > >  Greater Pollok Addiction Service   | Alcohol Addiction
> > >
> > >
> > >
> > > db=# select orgname from users where usertype='Alcohol
> > > Addiction' and usertype ='Drug Addiction';
> > >  orgname | usertype
> > > ---------+----------
> > > (0 rows)
> > >
> > > I would expect orgnames:
> > >  Greater Pollok Addiction Service,
> > > Rehab Remanufacturing Services Ltd
> > > to be returned.
> > > Any idea where I'm going wrong?
> > >
> > > Cheers
> > >
> > > Paul Butler
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>


In response to

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-10-17 16:12:18
Subject: Re: information
Previous:From: Chad ThompsonDate: 2002-10-17 15:22:17
Subject: Re: where clauses with and

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group