Forum OpenACS Q&A: Help with query with CONNECT BY and outer join

I ported this query, but I may have goofed up somewhere, so please check it out and if correct me where I am wrong (I'm posting this here so others can benefit from th example):

	  select 
            types.table_name, types.id_column, attr.attribute_name,
            attr.datatype
          from 
            acs_attributes attr,
            ( select 
                object_type, table_name, id_column, level as
inherit_level
              from 
                acs_object_types
              where 
                object_type <> 'acs_object'
              and
                object_type <> 'content_revision'
              connect by 
                prior supertype = object_type
              start with 
                object_type = :content_type) types        
          where 
            attr.object_type (+) = types.object_type
          order by 
            types.inherit_level desc

becomes:

	  select 
            types.table_name, types.id_column, attr.attribute_name,
            attr.datatype
          from 
            acs_attributes attr OUTER JOIN
            ( select 
                o2.object_type, o2.table_name, o2.id_column,
		tree_level(tree_sortkey)as inherit_level
              from
		( SELECT *
		  FROM acs_object_types
		  WHERE object_type = :content_type
		    AND object_type <> 'acs_object'
		    AND object_type <> 'content_revision'
		) o1,
                acs_object_types o2
              where 
		o2.tree_sortkey <= o1.tree_sortkey
		and o1.tree_sortkey like (o2.tree_sortkey || '%')

	    ) types USING (attr.object_type)
          where 
            attr.object_type = types.object_type
          order by 
            types.inherit_level desc
I think the following query will do the trick (untested):

          select 
                 types.table_name, types.id_column, attr.attribute_name,
                 attr.datatype
               from 
                 acs_attributes attr RIGHT OUTER JOIN
                 ( select 
                     ot2.object_type, ot2.table_name, ot2.id_column, 
                     tree_level(ot2.tree_sortkey) as inherit_level
                   from 
                     (select * 
                        from acs_object_types 
                       where object_type = :content_type) ot1,
                     acs_object_types ot2
                   where 
                     ot2.object_type <> 'acs_object'
                   and
                     ot2.object_type <> 'content_revision'
                   and 
                     ot2.tree_sortkey <= ot1.tree_sortkey
                   and
                     ot1.tree_sortkey like (ot2.tree_sortkey || '%')) types 
                 ON attr.object_type = types.object_type
               order by 
                 types.inherit_level desc


Collapse
Posted by Don Baccus on
Is there any reason you can't just say
<blockquote><pre>
using (object_type)
</pre></blockquote>rather than <blockquote><pre>
ON attr.object_type = types.object_type
</pre></blockquote>?
<p>
I kinda like the reward SQL92 gives us for giving identical keys identical names ...
No, I just didn't catch that.  The using form would be performed.
I meant preferred not performed.
Thanks Dan an Don. I suspected I had to put the condition out of the internal subquery, but wasn't sure.

One thing I didn't catch though, is why did it need to be a RIGHT outer join?

Outer joins have to be either right or left.  The outer keyword is just noise, so it can be dropped, but you must use the left or right keyword.  The left/right keyword is used to indicate which of the two joined tables is preserved.  In the above outer join the "types" table is preserved, because the "types" table is on the right side of the join statement.

You could also rewrite the query to have the types table first, in which case you would use a left outer join.

"attr right outer join types" is equivalent to "types left outer join attr".