Thread: Problem with query
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 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
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've tried adding st1.description to the SELECT list, and the GROUP BY clause, with no luck.
Susan
Susan Cassidy-3 wrote > I have a query with several joins, where I am searching for specific data > in certain columns. While accurate as far as describing a typical query it doesn't really tell us its intent.... > What is the first query doing wrong? No idea, though it may have something to do with not properly handling NULL, or, more likely, simply the "join order" semantics are wrong. The first thing i would do is breakup the query into smaller components each contained within their own CTE (WITH) section. I'd also minimize the number of OUTER JOINs, it is doubtful that you need as many as you seem to think. At best you should INNER JOIN first and then OUTER JOIN to the combination. I also rarely require the use of "DISTINCT ON" if I've coded my queries properly so removing that from the select-list would occur as well. As would probably adding at least a "COALESCE(s.description, st1.description)" somewhere. And outputting "si.description" and "st.description" causes an ambiguity since the resultant column name will not have the table prefix; typically only the column name survives on the outside (or higher up). > I've tried adding st1.description to the SELECT list, and the GROUP BY > clause, with no luck. And why did you think doing these things would have any impact on the number of rows returned (or, at least, returning some rows where previously there were none)? David j. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-query-tp5799696p5799700.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
What is the first query doing wrong?I get the results I expect (several hits).I have a query with several joins, where I am searching for specific data in certain columns. If I do this:No results are found, but if I just 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
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.descriptionI'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.
On 4/11/14, Chris Curvey <chris@chriscurvey.com> wrote: > On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy < > susan.cassidy@decisionsciencescorp.com> wrote: > >> I have a query with several joins, where I am searching for specific data >> in certain columns. Have you tried running each of your joins separately to see if there are row values common to both tables, ie: select count(*) from scenes s left outer join scene_thing_instances si on s.scene_id = si.scene_id then select count(*) from scene_thing_instances si left outer join scene_things st on si.scene_thing_id = st.scene_thing_id etc. I find when building complex queries (I've written some that ran over 100 lines and involved a dozen or more joined tables), I need to build them up, testing them as I build. -- Mike Nolan