There are two tables first Audit_exception and second finding_escalation. I need to join both the table using left outer join. Each record/exception in audit_exception table having corresponding record in finding_escalation table but there might be no corresponding records or having single record or multiple records. Basically 1:M relationship.

Tables

Audit_Exception

Finding_escalation

Requirement : I need to join both the table but only with latest escalation record if multiple record is present in finding_escalation table. If no record is present in finding_escalation table then according to left outer join the only data of Audit_exception should be in output result.

Problem : If I am using normal left outer join then I am getting multiple records in output query if finding_escalation table having multiple records for same finding. I can’t use ANSI standard LEFT OUTER JOIN ON keyword because query is written oracle based ( ) operator syntax. How to add extra condition on joining so that join will happen with finding_escalation latest record only.

Query 1 :

select * from audit_exception ae, finding_escalation esc 
where ae.objid = esc.finding_objid ( ) 
and ae.objid=1234;

This query is working fine when finding_escaltion table having no record or single record the output is single record. But resulting with multiple records when finding_escalation table also having multiple records.

Query 2:

select * from audit_exception ae, finding_escalation esc
where ae.objid = esc.finding_objid ( )
and esc.LATEST_RECORD = 1
and ae.objid=1234

This query is working fine when finding_escaltion table having single or multiple records. But not working if there is no records in finding_escaltion table, the output is blank.

My expectation : I need a single output irrespective of finding_escalation table has any record or multiple records or no records.

Please let me know if my query is not clear.

CodePudding user response：

Put the filter in the join condition:

SELECT *
FROM   audit_exception ae
       LEFT OUTER JOIN finding_escalation esc
       ON (    ae.objid = esc.finding_objid
           and esc.LATEST_RECORD = 1)
WHERE  ae.objid=1234

Or, using the legacy syntax (don't use it, convert to the ANSI/ISO syntax):

SELECT *
FROM   audit_exception ae,
       finding_escalation esc
WHERE  ae.objid = esc.finding_objid ( )
AND    esc.LATEST_RECORD ( ) = 1
AND    ae.objid=1234

Which, for the sample data:

CREATE TABLE Audit_Exception (Objid, Exception_id, Description, "COMMENT") AS
SELECT 1234, 1, 'Abcd', 'Abcd' FROM DUAL;

CREATE TABLE Finding_escalation (Objid, Finding_objid, Esc_level, Esc_type, Latest_record) AS
SELECT 10001, 1234, 1, 'Escalation', 1 FROM DUAL UNION ALL
SELECT 10002, 1234, 1, 'Warning',    0 FROM DUAL;

Both output:

OBJID	EXCEPTION_ID	DESCRIPTION	COMMENT	OBJID	FINDING_OBJID	ESC_LEVEL	ESC_TYPE	LATEST_RECORD
1234	1	Abcd	Abcd	10001	1234	1	Escalation	1

If you then do:

DELETE FROM finding_escalation;

Then the output is:

OBJID	EXCEPTION_ID	DESCRIPTION	COMMENT	OBJID	FINDING_OBJID	ESC_LEVEL	ESC_TYPE	LATEST_RECORD
1234	1	Abcd	Abcd	null	null	null	null	null

db<>fiddle here