Problem with query

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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