Re: where clauses with and

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