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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
