Problem with query

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

On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <
susan.cassidy@decisionsciencescorp.com> wrote:

I have a query with several joins, where I am searching for specific data
in certain columns. If I do this:

SELECT distinct on (s.description, st1.description, s.scene_id)
s.description, s.scene_id, to_char(s.time_of_creation, 'MM/DD/YY
HH24:MI:SS'),
position_0_0_0_info, st.scene_thing_id, si.description,
st.description, m.description
from scenes s
left outer join scene_thing_instances si on s.scene_id =
si.scene_id
left outer join scene_things st on si.scene_thing_id =
st.scene_thing_id
left outer join materials m on st.material_id = m.material_id
left outer join scene_things st1 on st.ultimate_parent_id =
st1.ultimate_parent_id
where st.description ilike '%bread%' or st1.description ilike
'%bread%'
group by s.description, st1.description, s.scene_id,
st.scene_thing_id, si.description, m.description order by s.description

No results are found, but if I just do this:

SELECT st.description, st1.description
from
scene_things st
left outer join scene_things st1 on st.ultimate_parent_id =
st1.ultimate_parent_id
where st1.description ilike '%bread%'
group by st.description, st1.description order by st.description

I get the results I expect (several hits).

What is the first query doing wrong?

I've tried adding st1.description to the SELECT list, and the GROUP BY
clause, with no luck.

Thanks,
Susan

First query goes

scenes -> scene_thing_instances -> scene_things

second query goes

scene_things -> scene_things

So they're not comparable queries.

My bet would be that scene_thing_instances is missing some rows that you
want/need.

--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.

On 4/11/14, Chris Curvey <chris@chriscurvey.com> wrote:

On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <
susan.cassidy@decisionsciencescorp.com> wrote:

I have a query with several joins, where I am searching for specific data
in certain columns.

Have you tried running each of your joins separately to see if there
are row values common to both tables, ie:

select count(*) from scenes s
left outer join scene_thing_instances si on s.scene_id =
si.scene_id

then

select count(*) from scene_thing_instances si
left outer join scene_things st on si.scene_thing_id =
st.scene_thing_id

etc.

I find when building complex queries (I've written some that ran over
100 lines and involved a dozen or more joined tables), I need to build
them up, testing them as I build.
--
Mike Nolan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general