Outer Join Syntax (compared to using *=)

Weller0123
Weller0123 used Ask the Experts™
on
I'm just starting to write some SQL again and I used to use the *= or outer joins to include null data.  I would like  to do what I'm doing in the first statement with 'left outer join' xxx 'on' xxx syntax.  Here's the original and then what I've tried.  I'm not sure how the inline view is affecting the problem here either.

---original  -----



select r.unique_name,
r.full_name,
etc.task_name,
SUM(AVAIL.avail) AVAIL,
SUM(ETC.etc) ETC
From
srm_resources r,
prj_resources prr,
(SELECT sres.unique_name, sres.full_name AS res_name, slice.slice_date AS period,
       slice.slice AS avail
  FROM srm_resources sres,
       prj_blb_slices slice,
       prj_blb_slicerequests slicereq
 WHERE sres.ID = slice.prj_object_id
   AND slice.slice_request_id = slicereq.ID
   AND slicereq.request_name = 'DAILYRESOURCEAVAILCURVE') AVAIL,

(SELECT sres.unique_name, sproj.unique_name AS proj_id, tsk.prname AS task_name,
       sres.full_name AS res_name, slice.slice_date AS period,
       slice.slice AS etc
  FROM srm_projects sproj,
       prtask tsk,
       prassignment asgnd,
       srm_resources sres,
       prj_blb_slices slice,
       prj_blb_slicerequests slicereq
 WHERE sproj.ID = tsk.prprojectid
   AND tsk.prid = asgnd.prtaskid
   AND asgnd.prresourceid = sres.ID
   AND asgnd.prid = slice.prj_object_id
   AND slice.slice_request_id = slicereq.ID
   AND slicereq.request_name = 'DAILYRESOURCEESTCURVE') ETC
where

r.unique_name = avail.UNIQUE_NAME and
r.id = prr.PRID and
r.is_active = '1' and
prr.prisrole = '0' and
avail.unique_name *= etc.unique_name and
avail.period *= etc.period

group by r.unique_name, r.full_name, etc.task_name

---new query that doesn't work--
select r.unique_name,
r.full_name,
etc.task_name,
SUM(AVAIL.avail) AVAIL,
SUM(ETC.etc) ETC
From
srm_resources r,
(SELECT sres.unique_name, sres.full_name AS res_name, slice.slice_date AS period,
       slice.slice AS avail
  FROM srm_resources sres,
       prj_blb_slices slice,
       prj_blb_slicerequests slicereq
 WHERE sres.ID = slice.prj_object_id
   AND slice.slice_request_id = slicereq.ID
   AND slicereq.request_name = 'DAILYRESOURCEAVAILCURVE') AVAIL
LEFT OUTER JOIN
(SELECT sres.unique_name, sproj.unique_name AS proj_id, tsk.prname AS task_name,
       sres.full_name AS res_name, slice.slice_date AS period,
       slice.slice AS etc
  FROM srm_projects sproj,
       prtask tsk,
       prassignment asgnd,
       srm_resources sres,
       prj_blb_slices slice,
       prj_blb_slicerequests slicereq
 WHERE sproj.ID = tsk.prprojectid
   AND tsk.prid = asgnd.prtaskid
   AND asgnd.prresourceid = sres.ID
   AND asgnd.prid = slice.prj_object_id
   AND slice.slice_request_id = slicereq.ID
   AND slicereq.request_name = 'DAILYRESOURCEESTCURVE') ETC

ON (etc.unique_name = avail.unique_name),

avail LEFT OUTER JOIN etc on (etc.period = avail.period)

-------------------
where

r.unique_name = AVAIL.UNIQUE_NAME and
r.is_active = '1'


group by r.unique_name, r.full_name, etc.task_name
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Something like this maybe. I don't think that it likes mixing the two join syntaxes

select r.unique_name, r.full_name, etc.task_name, SUM(AVAIL.avail) AVAIL, SUM(ETC.etc) ETC
from srm_resources r
  inner join prj_resources prr on prr.PRID = r.id and prr.prisrole = '0'
  inner join (select sres.unique_name, sres.full_name AS res_name, slice.slice_date AS period,
                     slice.slice AS avail
              from prj_blb_slicerequests slicereq 
                inner join prj_blb_slices slice on slice.slice_request_id = slicereq.ID
                inner join srm_resources sres on sres.ID = slice.prj_object_id
              where slicereq.request_name = 'DAILYRESOURCEAVAILCURVE') AVAIL
    on avail.UNIQUE_NAME = r.unique_name
  left outer join (select sres.unique_name, sproj.unique_name AS proj_id, tsk.prname AS task_name,
                     sres.full_name AS res_name, slice.slice_date AS period, slice.slice AS etc
              from prj_blb_slicerequests slicereq
                inner join prj_blb_slices slice on slice.slice_request_id = slicereq.ID
                inner join prassignment asgnd on asgnd.prid = slice.prj_object_id
                inner join srm_resources sres on sres.ID = asgnd.prresourceid
                inner join prtask tsk on tsk.prid = asgnd.prtaskid
                inner join srm_projects sproj on sproj.ID = tsk.prprojectid
             where slicereq.request_name = 'DAILYRESOURCEESTCURVE') ETC
    on etc.unique_name = avail.unique_name
    and etc.period = avail.period
where r.is_active = '1'
group by r.unique_name, r.full_name, etc.task_name

Open in new window

Top Expert 2012
Commented:
Do not use the deprecated *= operators, use LEFT JOIN instead as in:
SELECT  r.unique_name,
        r.full_name,
        etc.task_name,
        SUM(AVAIL.avail) AVAIL,
        SUM(ETC.etc) ETC
FROM    srm_resources r
        INNER JOIN prj_resources prr ON r.id = prr.PRID
        INNER JOIN (SELECT  sres.unique_name,
                            sres.full_name AS res_name,
                            slice.slice_date AS period,
                            slice.slice AS avail
                    FROM    srm_resources sres
                            INNER JOIN prj_blb_slices slice On sres.ID = slice.prj_object_id
                            INNER JOIN prj_blb_slicerequests slicereq ON slice.slice_request_id = slicereq.ID
                    WHERE   slicereq.request_name = 'DAILYRESOURCEAVAILCURVE'
                   ) AVAIL ON r.unique_name = avail.UNIQUE_NAME
        LEFT JOIN (SELECT   sres.unique_name,
                            sproj.unique_name AS proj_id,
                            tsk.prname AS task_name,
                            sres.full_name AS res_name,
                            slice.slice_date AS period,
                            slice.slice AS etc
                   FROM     srm_projects sproj
                            INNER JOIN prtask tsk ON sproj.ID = tsk.prprojectid
                            INNER JOIN prassignment asgnd ON tsk.prid = asgnd.prtaskid
                            INNER JOIN srm_resources sres ON asgnd.prresourceid = sres.ID
                            INNER JOIN prj_blb_slices slice ON asgnd.prid = slice.prj_object_id
                            INNER JOIN prj_blb_slicerequests slicereq ON slice.slice_request_id = slicereq.ID
                   WHERE    slicereq.request_name = 'DAILYRESOURCEESTCURVE'
                  ) ETC ON avail.unique_name = etc.unique_name
                           AND avail.period = etc.period
WHERE   r.is_active = '1'
        AND prr.prisrole = '0'
GROUP BY r.unique_name, r.full_name, etc.task_name

Open in new window

Author

Commented:
Okay, I was just wanting to make sure I could use the inline view with the left join.

Thanks,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial