pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubQueries
6+ messages / 4 participants
[nested] [flat]
* SubQueries
@ 1998-07-14 04:55 The Web Administrator <wwwadmin@wizard.ca>
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 05:01 Bruce Momjian <maillist@candle.pha.pa.us>
parent: The Web Administrator <wwwadmin@wizard.ca>
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 13:12 Walt Bigelow <walt@stimpy.com>
parent: 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 Tom Lane <tgl@sss.pgh.pa.us>
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 23:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Walt Bigelow <walt@stimpy.com>
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