Re: 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
>