Chirag Gandhi - 1 year ago 55
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  |
+------+------+------+------+
``````

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;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download