Re: where clauses with and - Mailing list 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@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


> -----Original Message-----
> From: Chad Thompson [mailto:chad@weblinkservices.com]
> Sent: 17 October 2002 05:22
> To: Rob; paul@entropia.co.uk; pgsql-novice@postgresql.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@jamwarehouse.com>
> To: <paul@entropia.co.uk>; <pgsql-novice@postgresql.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@jamwarehouse.com
> > +27 21 447 7440
> > Jam Warehouse RSA
> > Smart Business Innovation
> > http://www.jamwarehouse.com
> > **************************
> >
> >
> > > -----Original Message-----
> > > From: pgsql-novice-owner@postgresql.org
> > > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of paul butler
> > > Sent: 17 October 2002 02:43
> > > To: pgsql-novice@postgresql.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@postgresql.org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>