CODEWITHSUNDEEP
oracle-database
user1
user1

Reputation: 4131

Oracle Inner join on multiple tables returning null values

I am working on a sql inner join query which should return MAX elements from other tables. Tables B, C, D, E might be empty. In that case null should also be printed in output.

This is what I tried.

SELECT A.INDEX, A.CODE, MAX(B.DAY), MAX(C.TIMESTAMP), MAX(D.TIMESTAMP) 
FROM A 
INNER JOIN B 
INNER JOIN C
INNER JOIN D
INNER JOIN E
ON A.INDEX = B.INDEX
ON A.INDEX = C.INDEX
ON A.INDEX = D.INDEX
ON A.INDEX = E.INDEX AND E.FUNCTION = 0;

Table definitions:

A
-------
INDEX NOT NULL NUMBER(10)
CODE NOT NULL VARCHAR2(16)

B
--------
INDEX NUMBER(10)
DAY NUMBER(10)

C
---------
INDEX NUMBER(10)
TIMESTAMP TIMESTAMP(6)

D
---------
INDEX NUMBER(10)
TIMESTAMP TIMESTAMP(6)

E
----------
INDEX NUMBER(10)
FUNCTION NUMBER(5)

Upvotes: 0

Views: 1143

Answers (1)

Rahul
Rahul

Reputation: 77906

In that case, consider doing a LEFT OUTER JOIN instead like

FROM A 
LEFT JOIN B

So for your case

SELECT A.INDEX, A.CODE, MAX(B.DAY), MAX(C.TIMESTAMP), MAX(D.TIMESTAMP) 
FROM A 
LEFT JOIN B ON A.INDEX = B.INDEX
LEFT JOIN C ON A.INDEX = C.INDEX
LEFT JOIN D ON A.INDEX = D.INDEX
LEFT JOIN E ON A.INDEX = E.INDEX 
AND E.FUNCTION = 0;

Upvotes: 4

Related Questions