Re: Problem with query

[Chris Curvey <chris@xxxxxxxxxxxxxxx>]

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