Oracle SQL: Extracting data from multiple tables-CodePudding

I have 3 tables, say TabA, TabB and TabC. Below are some useful columns in these tables:

TabA(ID VARCHAR2 Primary Key, ..)

TabB(ID VARCHAR2, Value CHAR(1), LastUpdated Date)

TabC(ID VARCHAR2 Primary Key, Value CHAR(1), LastUpdated Date)

Here Value is a flag 'Y' or 'N'. I want to obtain all the IDs and their Value using these 3 tables. First of all I want to look into all the distinct IDs present in all the tables. Since the Value is not in TabA, I will look for the Value in TabB and TabC only. If for a particular ID, the Value is not there in any of the table, I will assume it 'N'. Suppose for a particular ID the value is in both TabB and TabC, I would like to take the Value where LastUpdated is greater.

I have tried using loops but this is not very efficient solution. I only need the Key and Value in the resultant cursors and want to keep a single query for this.

Can someone please help to identify a better solution than using loops.

Edit -

Here is a sample :

Suppose TabA is -

ID
100
101
102

TabB is -

ID	Value	LastUpdated
99	Y	21-May-22
100	N	22-May-22
103	N	23-May-22

TabC is -

ID	Value	LastUpdated
102	Y	20-May-22
103	Y	24-May-22
104	N	21-May-22

The result should be -

ID	Value	Why?
99	Y	from TabB
100	N	from TabB
101	N	In TabA only so defaulting N
102	Y	from TabC
103	Y	In TabB and TabC but LastUpdated is greater in TabC so taking TabC value
104	N	from TabC

Edit -

Expected result if an ID has same LastUpdated in TabB and TabC but different Values - This can be ignored as it will be a rare case. We can assume that this will never happen.

CodePudding user response：

You can try something like this:

SELECT id, value FROM
(SELECT id, value, ROW_NUMBER()
OVER (PARTITION BY id ORDER BY lastupdated DESC) AS rownr
FROM
(SELECT id, 'N' AS value, sysdate AS lastupdated FROM taba
WHERE id NOT IN (SELECT id FROM tabb) 
AND id NOT IN (SELECT id FROM tabc)
UNION
SELECT id, value, MAX(lastupdated) AS lastupdated
FROM tabb
GROUP BY id, value
UNION
SELECT id, value, MAX(lastupdated) AS lastupdated
FROM tabc
GROUP BY id, value
ORDER BY id, lastupdated DESC)) WHERE rownr = 1;

At least in my fiddle example, this works correctly: db<>fiddle

CodePudding user response：

Please define the expected result if an ID has same LastUpdated in TabB and TabC but different Values. In following it is undefined.

WITH BC AS (
    SELECT ID, VALUE FROM (
        SELECT ID, VALUE, 
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LastUpdated DESC) r
        FROM (
            SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
            FROM TabB
            GROUP BY ID, Value
            UNION ALL
            SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
            FROM TabC
            GROUP BY ID, Value
        )
    )
    WHERE R = 1
)
SELECT ID, VALUE FROM BC
UNION ALL
SELECT ID, 'N' VALUE FROM tabA A
WHERE A.ID NOT IN (
    SELECT ID FROM BC
)
ORDER BY ID;

CodePudding user response：

Use UNION to create a single data set from multiple tables. Then, filter on that data set.

SELECT ID, Value
FROM
(
    SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
    FROM TabB
    GROUP BY ID, Value
    UNION ALL
    SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
    FROM TabC
    GROUP BY ID, Value
) t
WHERE ID IN
(
    SELECT DISTINCT ID
    FROM TabA
)
AND
(
    Value = 'Y' OR
    NOT EXISTS
    (
        SELECT 1
        FROM
        (
            SELECT ID, Value
            FROM
            (
                SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
                FROM TabB
                GROUP BY ID, Value
                UNION ALL
                SELECT ID, Value, MAX(LastUpdated) AS LastUpdated
                FROM TabC
                GROUP BY ID, Value
            ) t
            WHERE ID = t.ID
        )
    )
)