Chirag Gandhi - 1 year ago 55

SQL Question

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`

`A.COL4`

`b.col3`

`a.col3`

`TableC`

`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 |

+------+------+------+------+

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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**