CODEWITHSUNDEEP
sqlrdbmsraima
stg
stg

Reputation: 2797

Left join with additional join condition on Raima

I am struggeling with a left join on a Raima database. I'd like to add an additional join condition, but in this case the join behaves like an inner join, thus I am losing some of the expected results.

Example:

TABLE_A

 ID        
-------    
 1         
 2         
 3         
 4

.
TABLE_B

 A_ID | B 
 --------
  1   | 1
  2   | 1 
  2   | 2
  3   | 2

Query

select * from TABLE_A left join TABLE_B
on TABLE_A.ID = TABLE_B.A_ID
and TABLE_B.B = 1

I am expecting the following result:

1   1   1
2   2   1
3 null null
4 null null

E.g. on an Oracle 11g I get the expected result, but on the Raima it shows me only the first two results. What is the problem here and how to fix it?

Upvotes: 0

Views: 83

Answers (1)

Utsav
Utsav

Reputation: 8093

You need this

select * from A left join 
(select * from B where B=1) bd
on A.ID = bd.A_ID

The query you gave will not give you expected result in oracle also. This will.

PS: Please use different names for table and column

Upvotes: 0

Related Questions