From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: Problem with query
Date: Fri, 11 Apr 2014 11:24:50 -0700 (PDT)
Message-ID: <1397240690337-5799700.post@n5.nabble.com> (raw)
In-Reply-To: <CAE3Q8onj+=+=b3+mSSikfJ8zbOTRH_5OTQ5RqVdNQ0OZW-fbrw@mail.gmail.com>
References: <CAE3Q8onj+=+=b3+mSSikfJ8zbOTRH_5OTQ5RqVdNQ0OZW-fbrw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Susan Cassidy-3 wrote
> I have a query with several joins, where I am searching for specific data
> in certain columns.
While accurate as far as describing a typical query it doesn't really tell
us its intent....
> What is the first query doing wrong?
No idea, though it may have something to do with not properly handling NULL,
or, more likely, simply the "join order" semantics are wrong.
The first thing i would do is breakup the query into smaller components each
contained within their own CTE (WITH) section. I'd also minimize the number
of OUTER JOINs, it is doubtful that you need as many as you seem to think.
At best you should INNER JOIN first and then OUTER JOIN to the combination.
I also rarely require the use of "DISTINCT ON" if I've coded my queries
properly so removing that from the select-list would occur as well.
As would probably adding at least a "COALESCE(s.description,
st1.description)" somewhere.
And outputting "si.description" and "st.description" causes an ambiguity
since the resultant column name will not have the table prefix; typically
only the column name survives on the outside (or higher up).
> I've tried adding st1.description to the SELECT list, and the GROUP BY
> clause, with no luck.
And why did you think doing these things would have any impact on the number
of rows returned (or, at least, returning some rows where previously there
were none)?
David j.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-query-tp5799696p5799700.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: Problem with query
In-Reply-To: <1397240690337-5799700.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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