pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SubQueries
6+ messages / 4 participants
[nested] [flat]

* SubQueries
@ 1998-07-14 04:55 The Web Administrator <wwwadmin@wizard.ca>
  1998-07-14 05:01 ` Re: [SQL] SubQueries Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: The Web Administrator @ 1998-07-14 04:55 UTC (permalink / raw)
  To: pgsql-sql

Aaaargg.. sub queries aren't implemented?
Any quick ideas how to get around this ..
UPDATE table SET time_out='now'
    WHERE time_in=(SELECT max(time_in) FROM table
        WHERE
            account_name='sparky')
    AND account_name='sparky';

--
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Michael - System Administrator              Working in Cheap Canadian
Dollars
Unix Administration - WebSite Hosting - Network Services - Programming
Wizard Internet Services - TechnoWizard Computers - Wizard Tower
TechnoServices
------------------------------------------------------------------------------

(604) 589-0037          Beautiful British Columbia, Canada
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] SubQueries
  1998-07-14 04:55 SubQueries The Web Administrator <wwwadmin@wizard.ca>
@ 1998-07-14 05:01 ` Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Bruce Momjian @ 1998-07-14 05:01 UTC (permalink / raw)
  To: wwwadmin@wizard.ca; +Cc: pgsql-sql

> Aaaargg.. sub queries aren't implemented?
> Any quick ideas how to get around this ..
> UPDATE table SET time_out='now'
>     WHERE time_in=(SELECT max(time_in) FROM table
>         WHERE
>             account_name='sparky')
>     AND account_name='sparky';

They are implemented in 6.3.2.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* subqueries
@ 1999-04-12 18:58 Walt Bigelow <walt@stimpy.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Walt Bigelow @ 1999-04-12 18:58 UTC (permalink / raw)
  To: pgsql-sql; walt@stimpy.com

I am having difficulites geting the following query to run properly:

I have 1 table that is called tbladdress that keeps company address
information AND the company's contacts (employees) in the same table. 
The company has a type id of 1, and the contact has a type id of 2.  The
contact aslo has a field filled in which points to the agencyid field of
the parent company.  Not every company has a contact.

The query I was trying to get working:

SELECT agencyid, first, last 
FROM tbladdress
WHERE type = 1
AND agencyid NOT IN
	(SELECT distinct agencylink
	FROM tbladdress 
	WHERE type = 2)
ORDER BY last;

This query returns 0 rows.  I manually walked through the data and
found, yes, there are company records that should have been returned
(about 150 of them).

Just to test I did the following:

CREATE table aa (name varchar(50), number int4);
CREATE table bb (name varchar(50), number int4);

I populated those tables with:
aa: Mildred | 5
    Sam     | 6

bb: Paul    | 1
    Sativa  | 2

and the select:

SELECT * 
FROM aa 
WHERE number not in (select number from bb);

and I get the desired response:
name   |number
-------+------
Mildred|    5
Sam    |    6

What am I doing wrong?  Can the SQL query not do a nested select on a
table it is currently doing a select on?

Thanks for any insight!

Walt
walt@stimpy.com



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] subqueries
@ 1999-04-13 15:37 Tom Lane <tgl@sss.pgh.pa.us>
  1999-04-13 13:12 ` Re: [SQL] subqueries Walt Bigelow <walt@stimpy.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 1999-04-13 15:37 UTC (permalink / raw)
  To: Walt Bigelow <walt@stimpy.com>; +Cc: pgsql-sql@hub.org

Walt Bigelow <walt@stimpy.com> writes:
> SELECT agencyid, first, last 
> FROM tbladdress
> WHERE type = 1
> AND agencyid NOT IN
> 	(SELECT distinct agencylink
> 	FROM tbladdress 
> 	WHERE type = 2)
> ORDER BY last;

Looks reasonable ...

> This query returns 0 rows [ when it shouldn't ].

Sounds like a bug.  What version of Postgres are you running?
What do you get from "EXPLAIN"ing that query?  What are the
data types of the columns being used?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] subqueries
  1999-04-13 15:37 Re: [SQL] subqueries Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-04-13 13:12 ` Walt Bigelow <walt@stimpy.com>
  1999-04-13 23:31   ` Re: [SQL] subqueries Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Walt Bigelow @ 1999-04-13 13:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org



On Tue, 13 Apr 1999, Tom Lane wrote:

> Walt Bigelow <walt@stimpy.com> writes:
> > SELECT agencyid, first, last 
> > FROM tbladdress
> > WHERE type = 1
> > AND agencyid NOT IN
> > 	(SELECT distinct agencylink
> > 	FROM tbladdress 
> > 	WHERE type = 2)
> > ORDER BY last;
> 
> Looks reasonable ...
> 
> > This query returns 0 rows [ when it shouldn't ].
> 
> Sounds like a bug.  What version of Postgres are you running?
> What do you get from "EXPLAIN"ing that query?  What are the
> data types of the columns being used?
> 

Tom,

I am using postgres 6.4.2 on a RH5.1 SMP box.

The output of explain:
NOTICE: QUERY PLAN:

Seq Scan on tbladdress  (cost=50.34 size=348 width=24)
  SubPlan
    ->  Unique  (cost=50.34 size=0 width=0)
          -> Sort  (cost=50.34 size=0 width=0)
               ->  Seq Scan on tbladdress  (cost=50.34 size=348 width=4)

EXPLAIN

The data types are int4 for both agencyid and agencylink.  And there are
about 800 rows total of 5 different types.  (the type field is int4 as
well)

Thanks again,
Walt





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] subqueries
  1999-04-13 15:37 Re: [SQL] subqueries Tom Lane <tgl@sss.pgh.pa.us>
  1999-04-13 13:12 ` Re: [SQL] subqueries Walt Bigelow <walt@stimpy.com>
@ 1999-04-13 23:31   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 1999-04-13 23:31 UTC (permalink / raw)
  To: Walt Bigelow <walt@stimpy.com>; +Cc: pgsql-sql@hub.org

Walt Bigelow <walt@stimpy.com> writes:
> On Tue, 13 Apr 1999, Tom Lane wrote:
>>>> This query returns 0 rows [ when it shouldn't ].

> The output of explain:
> NOTICE: QUERY PLAN:

> Seq Scan on tbladdress  (cost=50.34 size=348 width=24)
>   SubPlan
>     ->  Unique  (cost=50.34 size=0 width=0)
>           -> Sort  (cost=50.34 size=0 width=0)
>                ->  Seq Scan on tbladdress  (cost=50.34 size=348 width=4)

That looks about like what I'd expect (although I suppose you left
off the ORDER BY clause when doing the EXPLAIN?  With it, there should
be a top-level Sort node).

I'm not able to duplicate your problem here on a toy table, using either
6.4.2 or 6.5-current.  Perhaps there is some platform dependency
involved.  Can anyone else try it?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-04-13 23:31 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-14 04:55 SubQueries The Web Administrator <wwwadmin@wizard.ca>
1998-07-14 05:01 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-04-12 18:58 subqueries Walt Bigelow <walt@stimpy.com>
1999-04-13 15:37 Re: [SQL] subqueries Tom Lane <tgl@sss.pgh.pa.us>
1999-04-13 13:12 ` Re: [SQL] subqueries Walt Bigelow <walt@stimpy.com>
1999-04-13 23:31   ` Re: [SQL] subqueries Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox