Re: where clauses with and - Mailing list pgsql-novice

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
>