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

Rép. : where clauses with and

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@ (view raw or flat)
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)


pgsql-novice by date

Next:From: paul butlerDate: 2002-10-17 13:10:15
Subject: where clauses
Previous:From: dragillaDate: 2002-10-17 12:54:07
Subject: how to see a trigger

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