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