0

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?

stg
  • 2,757
  • 2
  • 28
  • 55
  • How does where condition `B.B =1` gives you expected result in `oracle`.? Seems incorrect to me. – Utsav Feb 02 '16 at 06:47
  • There is no `where` condition in my SQL statement. – stg Feb 02 '16 at 06:58
  • Ok I understand. Oracle is using `and TABLE_B.B = 1` in join condition but Raima is not(somehow. I dont know exactly why). Hence the discrepancy. To avoid it, please use the query like I mentioned in the answer. – Utsav Feb 02 '16 at 07:06

1 Answers1

0

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

Utsav
  • 7,914
  • 2
  • 17
  • 38
  • "the query you gave will not give you expected result in oracle also" ... Yes, it does! I tested it just a few minutes ago. Just wondering why it does not work on Raima – stg Feb 02 '16 at 06:57
  • Try the query which I gave in answer. – Utsav Feb 02 '16 at 06:59
  • Seems like Raima does not support such sub-selects. – stg Feb 02 '16 at 07:12
  • Strange as most db supports it. Try creating a view, say `bd` as `select * from B where B=1` and use that view in the `join`. – Utsav Feb 02 '16 at 07:16