Thread: where clauses with and

where clauses with and

From

"paul butler"

Date:

17 October 2002, 11:40:56

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

Re: where clauses with and

From

"Rob"

Date:

17 October 2002, 11:52:06

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@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of paul butler
> Sent: 17 October 2002 02:43
> To: pgsql-novice@postgresql.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@postgresql.org)
>

Re: where clauses with and

From

"paul butler"

Date:

17 October 2002, 12:26:32

I hope its normalised
its a many to many relationship between
organisations and usertypes:

CREATE TABLE "organisations" (
    "orgname" character varying(100) NOT NULL,
    etc
    Constraint "organisations_pkey" Primary Key ("orgname")
);

CREATE TABLE "usertype" (
  "usertype" varchar(50) NOT NULL,
  CONSTRAINT "usertype_pkey" PRIMARY KEY ("usertype")


CREATE TABLE "users" (
  "orgname" varchar(100) NOT NULL,
  "usertype" varchar(50) NOT NULL,
  CONSTRAINT "users_pkey" PRIMARY KEY ("orgname",
"usertype"),
  CONSTRAINT "<unnamed>" FOREIGN KEY ("orgname")
REFERENCES "organisations" ("orgname") ON DELETE
CASCADE ON UPDATE CASCADE NOT DEFERRABLE
INITIALLY IMMEDIATE,
  CONSTRAINT "<unnamed>" FOREIGN KEY ("usertype")
REFERENCES "usertype" ("usertype") ON DELETE NO ACTION
ON UPDATE CASCADE NOT DEFERRABLE INITIALLY
IMMEDIATE
) WITH OIDS;

am I making a terrible mistake here? I am dimly aware of
normalisation, functional dependencies, BCNF and all that. Is ther
a better way to approach this?

Cheers

Paul


>
> However I strongly suspect that the tables are not normalised.
> With out knowing more specific info I can't say exactly how to normalisem,
> but I'm guessing users should be split into a different table that refernces
> the organisation through some sort organtisation id.
> Also the usertype should probably be in a third table with the user table
> saying what kind of user they are and another table with org id and usertype
> id to say what type of users each organisation can handle.
> This is of course conjecture on my part. I think there are some docs about
> normalisation on techdocs.postgresl.org, if not any relational db theory
> book should handle it
> hth,
> - Stuart
>

Re: where clauses with and

From

"Chad Thompson"

Date:

17 October 2002, 14:22:57

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@jamwarehouse.com>
To: <paul@entropia.co.uk>; <pgsql-novice@postgresql.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@jamwarehouse.com
> +27 21 447 7440
> Jam Warehouse RSA
> Smart Business Innovation
> http://www.jamwarehouse.com
> **************************
>
>
> > -----Original Message-----
> > From: pgsql-novice-owner@postgresql.org
> > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of paul butler
> > Sent: 17 October 2002 02:43
> > To: pgsql-novice@postgresql.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@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: where clauses with and

From

"Rob"

Date:

17 October 2002, 14:33:29

> 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@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************


> -----Original Message-----
> From: Chad Thompson [mailto:chad@weblinkservices.com]
> Sent: 17 October 2002 05:22
> To: Rob; paul@entropia.co.uk; pgsql-novice@postgresql.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@jamwarehouse.com>
> To: <paul@entropia.co.uk>; <pgsql-novice@postgresql.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@jamwarehouse.com
> > +27 21 447 7440
> > Jam Warehouse RSA
> > Smart Business Innovation
> > http://www.jamwarehouse.com
> > **************************
> >
> >
> > > -----Original Message-----
> > > From: pgsql-novice-owner@postgresql.org
> > > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of paul butler
> > > Sent: 17 October 2002 02:43
> > > To: pgsql-novice@postgresql.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@postgresql.org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>