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.