CODEWITHSUNDEEP
sqloracle-databasejoin
Fayemrost
Fayemrost

Reputation: 51

Joining two tables at max date where conditions occur

I have two tables,

[TABLE_1]

| ID1 | ID2 | ID3 |
|-----+-----+-----|
| 200 | 125 | 300 |
| 206 | 128 | 650 |
| 230 | 543 | 989 |

[TABLE_2]

| ID1 | ID2 | ID3 |  Date  |
|-----+-----+-----+--------|
| 200 | 125 | 300 | 1/1/18 |
| 200 | 125 | 300 | 1/1/19 |
| 206 | 128 | 650 | 1/1/13 |
| 206 | 128 | 650 | 1/2/13 |
| 206 | 128 | 650 | 9/5/05 |

I'm trying to Left Join TABLE_1 to TABLE_2 while filtering the output so only rows where Date is at its maximum for those classifications are displayed. I simplified the data in my tables a little bit, but there's NO overall max date that can be used for all items in the table, the max date is unique to each item.

Desired results on the above example would be:

| ID1 | ID2 | ID3 |  Date  |
|-----+-----+-----+--------|
| 200 | 125 | 300 | 1/1/19 |
| 206 | 128 | 650 | 1/2/13 |

Here's my latest attempt at the query. It seems a little too complicated as I'm relatively new with SQL and has been running without giving a result for a long time now so I'm afraid it may be in an endless loop somehow:

SELECT DISTINCT *
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2
ON t1.ID1 = t2.ID1
AND t1.ID2 = t2.ID2
AND t1.ID3 = t2.ID3
WHERE t2.Date = (SELECT MAX(Date) FROM Table_2
    WHERE t1.ID1 = t2.ID2
    AND t1.ID2 = t2.ID2
    AND t1.ID3 = t2.ID3);

Any help on how better to query this will be greatly appreciated, thanks!

Notes: The column names are not identical (I know this would cause an error), I only labeled them like that for simplification.

Upvotes: 0

Views: 140

Answers (3)

TheDecks
TheDecks

Reputation: 86

You can also use the WITH statement, preaggregating the entries in table 2:

WITH agg_table_2 AS (
SELECT
     ID1
    ,ID2
    ,ID3
    ,MAX(DATE) AS MAX_DATE
FROM TABLE_2
GROUP BY
     ID1
    ,ID2
    ,ID3
)
SELECT
     T1.ID1
    ,T1.ID2
    ,T1.ID3
    ,T2.MAX_DATE
FROM TABLE_1 T1
--LEFT
JOIN TABLE_2 T2
ON   T1.ID1 = T2.ID1
AND  T1.ID2 = T2.ID2
AND  T1.ID3 = T2.ID3
;

Note that I actually commented out the LEFT in JOIN as in your desired output 230, 543, 989 was not present. Uncomment it, if you want to keep that entry with NULL value assigned.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

So as per the comments, I think the following is the complete code:

SELECT
    T1.ID1,
    T1.ID2,
    T1.ID3,
    MAX(T2.DATE) AS DATE
FROM
    TABLE1 T1
    -- USED LEFT JOIN AS NOT SURE IF THERE IS ATLEAST ONE ROW IN T2 FOR EACH ROW IN T1
    LEFT JOIN ( 
        SELECT
            ID1,
            ID2,
            ID3,
            MAX(DATE)
        FROM
            TABLE2
        GROUP BY
            ID1,
            ID2,
            ID3
    ) T2 ON T2.ID1 = T1.ID1
            AND T2.ID2 = T1.ID2
                AND T2.ID3 = T1.ID3;

Cheers!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

For your given results, why not just aggregate table 2?

select id1, id2, id3, max(date)
from table2
group by id1, id2, id3;

If you need to filter this only for the triples in table1, then:

select t2.id1, t2.id2, t2.id3, max(t2.date)
from table2 t2 join
     table1 t1
     on t2.id1 = t1.id1 and t2.id2 = t1.id2 and t2.id3 = t1.id3
group by t2.id1, t2.id2, t2.id3;

Upvotes: 2

Related Questions