Re: Problem with query

On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <
susan(dot)cassidy(at)decisionsciencescorp(dot)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.