From: | "Erwan DUROSELLE" <EDuroselle(at)seafrance(dot)fr> |
---|---|
To: | <paul(at)entropia(dot)co(dot)uk>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Rép. : where clauses with and |
Date: | 2002-10-17 12:57:45 |
Message-ID: | 82f4392b8bacb6c24fc5f04eb9e890623daeb5a6@ |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
>> Any idea where I'm going wrong?
Sure!
You must think in term of row in the table: in your query, you are looking for a line in the table whose single usertype string would be BOTH 'Alcohool..' and 'Drug...', which is impossible.
So you have to select a first list with Alcohol, a second with Drug and look who is in the two of them
try ( not tested):
select orgname from users u1, users u2
where
u1.orgname=u2.orgname
and
u1.usertype = 'Drug Addiction'
and
u2.usertype = 'Alcohol Addiction' ;
or
select orgname from users where usertype = 'Drug Addiction' or usertype='Alcohol addiction'
having count(*) =2;
Erwan
-------------------------------------------------------------------------------
Erwan DUROSELLE // SEAFRANCE DSI
Responsable Bases de Données // Databases Manager
Tel: +33 (0)1 55 31 59 70 // Fax: +33 (0)1 55 31 85 28
email: eduroselle(at)seafrance(dot)fr
-------------------------------------------------------------------------------
>>> "paul butler" <paul(at)entropia(dot)co(dot)uk> 17/10/2002 14:42 >>>
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)
From | Date | Subject | |
---|---|---|---|
Next Message | paul butler | 2002-10-17 13:10:15 | where clauses |
Previous Message | dragilla | 2002-10-17 12:54:07 | how to see a trigger |