| From: | Susan Cassidy <susan(dot)cassidy(at)decisionsciencescorp(dot)com> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Problem with query |
| Date: | 2014-04-11 17:50:50 |
| Message-ID: | CAE3Q8onj+=+=b3+mSSikfJ8zbOTRH_5OTQ5RqVdNQ0OZW-fbrw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andy Colson | 2014-04-11 18:12:15 | Re: efficient way to do "fuzzy" join |
| Previous Message | David G Johnston | 2014-04-11 17:39:10 | Re: Need query |