I've got
SELECT
CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY
and want to add to each entry CONTENTNAME
column from table CONTENT
.
I've tried:
SELECT
CONNECT_BY_ROOT TCT.CONTENTNAME AS ENDNODECONTENTNAME
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TCT.CONTENTNAME
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
LEFT OUTER JOIN CONTENT TCT ON TCT.IDCONTENT = TAXY.IDCONTENT
CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY
Expecting the same results, only with CONNECT_BY_ROOT
using 1 extra column: CONTENTNAME
from CONTENT
, associated by IDCONTENT
; which should be NULL
if there is no associated content (IDCONTENT
is NULL
)
Instead I only get the rows where IDCONTENT
is not NULL
. What can I do to get those rows too?
I've also tried:
SELECT
CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TCT.CONTENTNAME
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
LEFT OUTER JOIN CONTENT TCT ON TCT.IDCONTENT = TAXY.IDCONTENT
CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY
And I've also tried
SELECT
SYS_CONNECT_BY_PATH(TAXY.CATEGORYNAME, ' \ ') AS BREADCRUMBSPATH
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TCT.CONTENTNAME
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
LEFT OUTER JOIN CONTENT TCT ON TCT.IDCONTENT = TAXY.IDCONTENT
START WITH TAXY.IDPARENTCATEGORY IS NULL CONNECT BY TAXY.IDPARENTCATEGORY = PRIOR TAXY.IDCATEGORY
but I get the same results (only values where IDCONTENT
is not NULL
)
The most weird of it all is that the query was working as expected yesterday on development DB and now it is not. So I've created a fiddle to check if I could reproduce previous results and it works fine... http://sqlfiddle.com/#!4/ecb9d/1 http://sqlfiddle.com/#!4/ecb9d/3
What could have caused this change of behavior?
I still don't know what happened (still not working) but this solution seems to work fine in both cases:
SELECT IDBRANCH, ENDNODEIDCONTENT, IDCATEGORY, IDPARENTCATEGORY, TAXYJ.IDCONTENT, TCT.CONTENTNAME AS ENDNODECONTENTNAME, CATEGORYNAME, LVL
FROM (
SELECT CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH
, CONNECT_BY_ROOT TAXY.IDCONTENT AS ENDNODEIDCONTENT
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY
) TAXYJ
LEFT OUTER JOIN CONTENT TCT ON TCT.IDCONTENT = TAXYJ.ENDNODEIDCONTENT