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.