CODEWITHSUNDEEP
sql
DavidOl
DavidOl

Reputation: 1

BO SQL query: LEFT JOIN

I am trying to display the ID of table B which is in table A and display null for the ID of table B without ID in table A.

This query works without problems under sql developer:

SELECT a.file_name, a.DATE, a.ID, a.number_line, 
       b.file_name, b.DATE, b.ID, b.number_line
FROM TABLE a LEFT JOIN
     TABLE b
     ON a.ID = b.ID AND
        b.file_name LIKE 'EEEEEE%' AND
        b.DATE BETWEEN '2019-10-01' AND '2020-01-01'
WHERE a.file_name LIKE 'RRRRRR%';

But the problem it does not work under business object, the software suggests writing the request this way, and it only displays rows from table B which contains IDs from table A without the null:

SELECT a.file_name, a.DATE, a.ID, a.number_line,
 b.file_name, b.DATE, b.ID, b.number_line 
 FROM TABLE a, TABLE b 
 WHERE ( a.id(+)=b.id ) AND 
 ( a.file_name LIKE 'RRRRRR%' AND b.DATE BETWEEN '2019-10-01' 
 AND '2020-01-01' AND b.file_name LIKE 'EEEEEE%');

How to translate this request?

Upvotes: 0

Views: 189

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If the problem is inequalities in the LEFT JOIN condition, you can use a subquery:

SELECT a.file_name, a.DATE, a.ID, a.number_line, 
       b.file_name, b.DATE, b.ID, b.number_line
FROM TABLE a LEFT JOIN
     (SELECT b.*
      FROM TABLE b
      WHERE b.file_name LIKE 'EEEEEE%' AND
            b.DATE BETWEEN '2019-10-01' AND '2020-01-01'
     ) b
     ON a.ID = b.ID       
WHERE a.file_name LIKE 'RRRRRR%';

If you have to use that archaic syntax, you can apply that using a subquery as well.

Upvotes: 1

Salim
Salim

Reputation: 2178

The following style being asked by BO is old way of writing left-outer join where + sign is used on all conditions of the left joined table.

You need to put + on all conditions of the left table. Please try the following. 

SELECT a.file_name, a.DATE, a.ID, a.number_line,
 b.file_name, b.DATE, b.ID, b.number_line 
 FROM TABLE a, TABLE b 
 WHERE ( a.id(+)=b.id ) AND 
 ( a.file_name LIKE 'RRRRRR%' (+) AND b.DATE BETWEEN '2019-10-01' 
 AND '2020-01-01' AND b.file_name LIKE 'EEEEEE%');

On your original SQL I think you should move all filter criteria except join condition outside. Please see below,

SELECT a.file_name, a.DATE, a.ID, a.number_line, 
       b.file_name, b.DATE, b.ID, b.number_line
FROM TABLE a LEFT JOIN
     TABLE b
     ON a.ID = b.ID AND        
WHERE a.file_name LIKE 'RRRRRR%'
AND b.file_name LIKE 'EEEEEE%' AND b.DATE BETWEEN '2019-10-01' AND '2020-01-01'
;

Upvotes: 0

Related Questions