I have a table CHANNELS that can be self-joined (some channels have parent channels), so the following statement retrieves joined information on both channels and its parents:
Code: Select all
SELECT *
FROM CHANNELS c1
LEFT OUTER JOIN CHANNELS c2 ON c1.PARENT_CHANNEL_ID = c2.CHANNEL_ID
Code: Select all
var query =
from c in ctx.Channels
join cp in ctx.Channels.DefaultIfEmpty() on c.ParentChannelId equals cp.ChannelId
select new {c,cp};
Code: Select all
SELECT *
FROM CHANNELS "Extent1"
INNER JOIN (SELECT
"SingleRowTable1".X,
"Extent2".CHANNEL_ID,
"Extent2".CHANNEL_NAME,
"Extent2".PARENT_CHANNEL_ID
FROM ( SELECT 1 AS X FROM DUAL) "SingleRowTable1"
LEFT OUTER JOIN CHANNELS "Extent2" ON 1 = 1 ) "Join1" ON ("Extent1".PARENT_CHANNEL_ID = "Join1".CHANNEL_ID) OR (("Extent1".PARENT_CHANNEL_ID IS NULL) AND ("Join1".CHANNEL_ID IS NULL))
We are using Oracle 11 and the DotConnect version is 9.6.570.
Is this a know issue or is there other way to express LEFT OUTER JOIN?