Rép. : where clauses with and

>> 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
usertypestring 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@seafrance.fr
-------------------------------------------------------------------------------


>>> "paul butler" <paul@entropia.co.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@postgresql.org)