Hello all,
Here is the original SQL statement that did not need OUTER JOIN functionality, very basic all INNER:
SELECT DISTINCT
a.group_id,
a.workorder_no,
b.assignment_no,
d.document_no,
d.source,
b.assignment_type,
b.assignment_class,
b.setid,
b.service_cust_id,
b.service_addr_num,
d.image_file_name,
b.start_dt,
b.end_dt,
d.week_end_dt,
b.business_type
FROM sysadm.ps_pb_wkord a,
sysadm.ps_pb_wkord_assig b,
sysadm.ps_pb_ts_control c,
sysadm.ps_pb_ts_header d
WHERE a.group_id = b.group_id
AND a.workorder_no = b.workorder_no
AND a.group_id = c.group_id
AND b.assignment_no = c.assignment_no
AND c.group_id = d.group_id
AND c.control_no = d.control_no
However, now the business rules need an outer join on the join of 'b.assignment_no = c.assignment_no' to retrieve assignments
that are in b but no match in c. OUTER JOIN, right?. Seperate queries against the tables prove that there are quite a few records in table b that are not in table c. Here is the new query with the OUTER JOIN that I thought would remedy this:
SELECT DISTINCT
a.group_id,
a.workorder_no,
b.assignment_no,
d.document_no,
d.source,
b.assignment_type,
b.assignment_class,
b.setid,
b.service_cust_id,
b.service_addr_num,
d.image_file_name,
b.start_dt,
b.end_dt,
d.week_end_dt,
b.business_type
FROM sysadm.ps_pb_wkord_assig b
LEFT OUTER JOIN
sysadm.ps_pb_ts_control c
ON
b.assignment_no = c.assignment_no,
sysadm.ps_pb_wkord a,
sysadm.ps_pb_ts_header d
WHERE a.group_id = b.group_id
AND a.workorder_no = b.workorder_no
AND a.group_id = c.group_id
AND c.group_id = d.group_id
AND c.control_no = d.control_no
This query however produces the exact same results as the query without the OUTER JOIN. Is the problem that the other tables involved that do not have to be OUTER JOINED need to use the new 9i INNER JOIN syntax? Basically, how do you OUTER JOIN some tables while INNER JOINING the others. Thanks.
Start Free Trial
