Search Postgresql Archives

Problem with query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux