pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL help
9+ messages / 8 participants
[nested] [flat]
* SQL help
@ 1998-11-07 22:55 Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Mather (Powered By Linux) @ 1998-11-07 22:55 UTC (permalink / raw)
To: pgsql-novice@postgreSQL.org; pgsql-sql
G'day All,
I'm having a bit of trouble with a couple of SQL statements and hope
someone can help.
Firstly can postgresql handle JOINs ? I can't seem to find anything
about it.
more importantly:
I have a small db set up as follows:
EMPLOYEES
employee_id
name
address
salary
PROJECTS
project_id
project_name
project_location
WORKS_ON
employee_id
project_id
job
rating
I need to find:
All employees who work on every project.
(easy enough to find all who work on any particular project, but not
those who work on EVERY project)
distinct employees whose salary is higher than their managers.
Thanks in anticipation
Andrew
--------------------------------------------------------
Andrew Mather: System Administrator, VIAS
mathera@woody.agvic.gov.au / amsm@deakin.edu.au
http://www.deakin.edu.au/~amsm/index.html
Ph: 03 92174342 (BH), Mob 0413 009 761
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
All I need is a Wave and a board to surf it on.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-
^ permalink raw reply [nested|flat] 9+ messages in thread
* SQL Help
@ 2001-01-26 19:11 Mark A. Summers <msummers@irtnet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mark A. Summers @ 2001-01-26 19:11 UTC (permalink / raw)
To: pgsql-sql
I am having trouble with the following query taking forever:
-----
SELECT * FROM ret108108_00, product
WHERE ret108108_00."isbn" = product."Item1"
AND product."SuperCategory" = '1'
AND product."PublisherCode" = 'ZON'
ORDER BY ret108108_00.qty DESC LIMIT 100
----
The problem is the second AND -- if I take this out it runs fine -- is there
any way to optimize it ? I had a similar problem with just Category so I
created a new field in the product file called FullCat which combined
SuperCategory and Category and thus eliminating the 2nd AND clause -- and
that fixed it -- I just didn't want to do the same thing with Publisher.
Before I tried to index Category, SuperCategory ... nothing seemed to help
Any ideas
Thanks in advance
Mark Summers
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help
@ 2001-01-27 01:48 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Mark A. Summers <msummers@irtnet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2001-01-27 01:48 UTC (permalink / raw)
To: Mark A. Summers <msummers@irtnet.com>; +Cc: pgsql-sql
On Fri, 26 Jan 2001, Mark A. Summers wrote:
> I am having trouble with the following query taking forever:
> -----
> SELECT * FROM ret108108_00, product
> WHERE ret108108_00."isbn" = product."Item1"
>
> AND product."SuperCategory" = '1'
> AND product."PublisherCode" = 'ZON'
> ORDER BY ret108108_00.qty DESC LIMIT 100
> ----
> The problem is the second AND -- if I take this out it runs fine -- is there
> any way to optimize it ? I had a similar problem with just Category so I
> created a new field in the product file called FullCat which combined
> SuperCategory and Category and thus eliminating the 2nd AND clause -- and
> that fixed it -- I just didn't want to do the same thing with Publisher.
> Before I tried to index Category, SuperCategory ... nothing seemed to help
Have you run a VACUUM ANALYZE on the table? And what does
EXPLAIN show for the query?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help
@ 2001-01-29 16:29 Mark A. Summers <msummers@irtnet.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 9+ messages in thread
From: Mark A. Summers @ 2001-01-29 16:29 UTC (permalink / raw)
To: pgsql-sql
I am loser -- I didn't realize there was analyze option on vacuum -- I
created another index on Publisher code -- Now it smokes
thanks
Mark Summers
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:Pine.BSF.4.21.0101261746560.98620-100000@megazone23.bigpanda.com...
>
> On Fri, 26 Jan 2001, Mark A. Summers wrote:
>
> > I am having trouble with the following query taking forever:
> > -----
> > SELECT * FROM ret108108_00, product
> > WHERE ret108108_00."isbn" = product."Item1"
> >
> > AND product."SuperCategory" = '1'
> > AND product."PublisherCode" = 'ZON'
> > ORDER BY ret108108_00.qty DESC LIMIT 100
> > ----
> > The problem is the second AND -- if I take this out it runs fine -- is
there
> > any way to optimize it ? I had a similar problem with just Category so
I
> > created a new field in the product file called FullCat which combined
> > SuperCategory and Category and thus eliminating the 2nd AND clause --
and
> > that fixed it -- I just didn't want to do the same thing with Publisher.
> > Before I tried to index Category, SuperCategory ... nothing seemed to
help
>
> Have you run a VACUUM ANALYZE on the table? And what does
> EXPLAIN show for the query?
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* SQL Help
@ 2003-05-30 15:47 C F <tacnaboyz@yahoo.com>
0 siblings, 3 replies; 9+ messages in thread
From: C F @ 2003-05-30 15:47 UTC (permalink / raw)
To: pgsql-sql
Hello,
I already tried this same basic question with no response.... maybe I was too wordy. So here it is simplified.... what's the best way to write this query? I'm open to using stored procedures, but even then I don't know how I would conditionally populate a resultset (refcursor). Notice that in the first three cases, the expression is the exact same, only the return value is different. This seems inefficient....
select
(case when column1 = column2 then column3 end) as alias1,
(case when column1 = column2 then column4 end) as alias2,
(case when column1 = column2 then column5 end) as alias3,
(case when column6 = column7 then column8 end) as alias4
from
mytable
;
Any ideas?
Thanks!
---------------------------------
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help
@ 2003-05-30 16:30 Bruno Wolff III <bruno@wolff.to>
parent: C F <tacnaboyz@yahoo.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2003-05-30 16:30 UTC (permalink / raw)
To: C F <tacnaboyz@yahoo.com>; +Cc: pgsql-sql
On Fri, May 30, 2003 at 08:47:03 -0700,
C F <tacnaboyz@yahoo.com> wrote:
> Hello,
> I already tried this same basic question with no response.... maybe I was too wordy. So here it is simplified.... what's the best way to write this query? I'm open to using stored procedures, but even then I don't know how I would conditionally populate a resultset (refcursor). Notice that in the first three cases, the expression is the exact same, only the return value is different. This seems inefficient....
>
>
> select
> (case when column1 = column2 then column3 end) as alias1,
> (case when column1 = column2 then column4 end) as alias2,
> (case when column1 = column2 then column5 end) as alias3,
> (case when column6 = column7 then column8 end) as alias4
> from
> mytable
> ;
That seems pretty efficient. I wouldn't expect the repeated column1 = column2
tests to add much overhead. Unless there is more to the story this looks
to be what you want to do.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help
@ 2003-05-30 16:37 Josh Berkus <josh@agliodbs.com>
parent: C F <tacnaboyz@yahoo.com>
2 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-05-30 16:37 UTC (permalink / raw)
To: C F <tacnaboyz@yahoo.com>; pgsql-sql
CF,
> select
> (case when column1 = column2 then column3 end) as alias1,
> (case when column1 = column2 then column4 end) as alias2,
> (case when column1 = column2 then column5 end) as alias3,
> (case when column6 = column7 then column8 end) as alias4
> from
> mytable
Given the information you've given us (including your first e-mail), I can't
think of a better way to get the output you want than the above. You could
work through subselects, etc, but I think that would be even less efficient.
One thing you might want to think about is your whole approach to designing
this application; it may be that you can simplify your queries by changing
your table design.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help
@ 2003-05-30 17:12 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: C F <tacnaboyz@yahoo.com>
2 siblings, 0 replies; 9+ messages in thread
From: Franco Bruno Borghesi @ 2003-05-30 17:12 UTC (permalink / raw)
To: C F <tacnaboyz@yahoo.com>; pgsql-sql
If your concern is speed, the thing here is that you will have as many records
as there are in "mytable", most of them (I think) with NULLs for alias1, alias2,
alias3 and alias4. This way, there is no condition to filter any record, so
postgreSQL will do a sequential scan over the whole table.
If you are ok fetching the records that match and assumming that the all the others
don't match, then the following will work:
--column1=column2
SELECT
column3 AS alias1, column4 AS alias2, column5 AS alias3, NULL AS alias4
FROM
myTable
WHERE
column1=column2
UNION
--column6=column7
SELECT
NULL AS alias1, NULL AS alias2, NULL AS alias3, column8 AS alias4
FROM
myTable
WHERE
column6=column7
Of course, you will need the necesary indexes.
If this didn't give you a hint, please post a message with a link to your original
message, so I can get a better idea of what you need.
On Friday 30 May 2003 12:47, C F wrote:
> Hello,
> I already tried this same basic question with no response.... maybe I was
> too wordy. So here it is simplified.... what's the best way to write this
> query? I'm open to using stored procedures, but even then I don't know how
> I would conditionally populate a resultset (refcursor). Notice that in the
> first three cases, the expression is the exact same, only the return value
> is different. This seems inefficient....
>
>
> select
> (case when column1 = column2 then column3 end) as alias1,
> (case when column1 = column2 then column4 end) as alias2,
> (case when column1 = column2 then column5 end) as alias3,
> (case when column6 = column7 then column8 end) as alias4
> from
> mytable
> ;
>
> Any ideas?
> Thanks!
>
>
> ---------------------------------
> Do you Yahoo!?
> Free online calendar with sync to Outlook(TM).
^ permalink raw reply [nested|flat] 9+ messages in thread
* SQL Help
@ 2003-11-05 18:30 luisblock@amauta.rcp.net.pe
0 siblings, 0 replies; 9+ messages in thread
From: luisblock@amauta.rcp.net.pe @ 2003-11-05 18:30 UTC (permalink / raw)
To: pgsql-sql
PLS, help,
I am trying to JOIN two tables together with the LEFT OUTER JOIN statement, but
with a twist:
1. table 1 id and table 2 projectid are the join field
2. Table 1 has de detail info for several ocurrences in table 2. For each id
in table 1 (project detail), there are many entries of projectid on table 2
(update log on project)
3. I need a query that gets each project detail (table 1) with ONLY the LAST
(most recent) update date and comment (table 2) for that project.
The result report will look something like this:
Repeat____________________________________________________
PROJECT NAME(table 1) PROJECT DATE(table 1) PROJECT OWNER(table 1)
LAST UPDATE(table 2) LAST UPDATE COMMENT(table 2)
Repeat_____________________________________________________
I have used the SELECT TOP 1 function, the DISTINCT function, I am out of ideas.
Thanx
LuisFe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-11-05 18:30 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-07 22:55 SQL help Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
2001-01-26 19:11 SQL Help Mark A. Summers <msummers@irtnet.com>
2001-01-27 01:48 ` Re: SQL Help Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-29 16:29 ` Re: SQL Help Mark A. Summers <msummers@irtnet.com>
2003-05-30 15:47 SQL Help C F <tacnaboyz@yahoo.com>
2003-05-30 16:30 ` Re: SQL Help Bruno Wolff III <bruno@wolff.to>
2003-05-30 16:37 ` Re: SQL Help Josh Berkus <josh@agliodbs.com>
2003-05-30 17:12 ` Re: SQL Help Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-11-05 18:30 SQL Help luisblock@amauta.rcp.net.pe
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