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

Nested Queries

From: Luke Tudor - RSG <ltt(at)pml(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Nested Queries
Date: 2001-01-17 12:16:34
Message-ID: 3A658D22.B926DD17@unixmail.npm.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I seem to have a small problem with my nested queries. Im sure its
something so simple; If I run each query seperatly, I get the correct
results and these results can be put into the parent query. 
However if the queries are run together as one nested query the results
take
hours and hours (more like days).

Why? Where am I going wrong?

This doen't work:


SELECT * 
FROM PERMISSION p 
WHERE p.permissionno IN (

   SELECT PERMISSIONNO 
   FROM AGREEMENTPERMISSION ap 
   WHERE ap.agreementid IN (

      SELECT AGREEMENTID 
      FROM WEBAGREEMENT wa 
      WHERE wa.webusername = 'testuser'
   )
);


This does work;

SELECT AGREEMENTID 
FROM WEBAGREEMENT wa 
WHERE wa.webusername = 'testuser'

results: 

agreementid
-----------
       5924
       5925
       5987
       
then using

SELECT PERMISSIONNO 
FROM AGREEMENTPERMISSION ap 
WHERE ap.agreementid IN (5924, 5925, 5987)

and so on.

Any help appriciated
Thanks again.
   
-- 

Best Regards,
Luke.

"Its easier to try than to prove it can't be done" ~ Justin Hayward (The
Moody Blues)

Responses

pgsql-novice by date

Next:From: John PoltorakDate: 2001-01-17 12:29:31
Subject: Re: What am I doing wrong?
Previous:From: John PoltorakDate: 2001-01-17 12:08:35
Subject: Re: What am I doing wrong?

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