Chirag Gandhi Chirag Gandhi - 5 months ago 9
SQL Question

Joining tables on complex conditions

I have two tables:

CREATE TABLE TableA ( Col1, Col2, Col3, Col4 ) AS
SELECT 'a', 'b', 1, 'ABC' FROM DUAL UNION ALL
SELECT 'a', 'b', 2, 'DEF' FROM DUAL UNION ALL
SELECT 'a', 'b', 15, 'GHI' FROM DUAL UNION ALL
SELECT 'c', 'd', 17, 'JKL' FROM DUAL UNION ALL
SELECT 'c', 'd', 10, 'MNO' FROM DUAL UNION ALL
SELECT 'c', 'd', 6, 'PQR' FROM DUAL;

CREATE TABLE TableB ( Col1, Col2, Col3 ) AS
SELECT 'a', 'b', 1 FROM DUAL UNION ALL
SELECT 'a', 'b', 4 FROM DUAL UNION ALL
SELECT 'a', 'b', 12 FROM DUAL UNION ALL
SELECT 'c', 'd', 9 FROM DUAL UNION ALL
SELECT 'c', 'd', 12 FROM DUAL UNION ALL
SELECT 'c', 'd', 3 FROM DUAL;


The output is
A.COL1, A.COL2,B.COL3
AND
A.COL4
such that value of
b.col3
is the maximum value lower than
a.col3
.
TableC
is the output table as below.

TableC
+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| a | b | 1 | ABC |
| a | b | 1 | DEF |
| a | b | 12 | GHI |
| c | d | 12 | JKL |
| c | d | 9 | MNO |
| c | d | 3 | PQR |
+------+------+------+------+

MT0 MT0
Answer

In this case, it is simpler to use a correlated sub-query rather than to join the tables:

Query:

SELECT A.Col1,
       A.Col2,
       ( SELECT MAX( B.Col3 )
         FROM   TableB B
         WHERE  A.Col1 = B.Col1
         AND    A.Col2 = B.Col2
         AND    A.Col3 > B.Col3 ) AS Col3,
       A.Col4
FROM   TableA A;

Output:

COL1 COL2 COL3 COL4
---- ---- ---- ----
a    b    NULL ABC
a    b       1 DEF
a    b      12 GHI
c    d      12 JKL
c    d       9 MNO
c    d       3 PQR

If you want the first row to have COL3 to equal 1 then you need to use >= rather than > (but this was not the logic described in your text).

If you want to filter out the NULL row then use:

SELECT *
FROM   ( ... above query ... )
WHERE  Col3 IS NOT NULL;