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

Re: where clauses with and

From: "Chad Thompson" <chad(at)weblinkservices(dot)com>
To: "Rob" <rob(at)jamwarehouse(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:22:17
Message-ID: 06ce01c275f0$fb6ab370$32021aac@chad (view raw or flat)
Thread:
Lists: 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(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

Responses

pgsql-novice by date

Next:From: RobDate: 2002-10-17 15:33:14
Subject: Re: where clauses with and
Previous:From: Mattia BocciaDate: 2002-10-17 13:48:11
Subject: information

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