Fetch records with outer join query-CodePudding

I have a situation where current implementation joins 2 tables to fetch records as below however there is a case that employee and address as versioned entities


    create table employee (
  empid int primary key,
  VER_ID int,
  ADId int 
);

create table ADDRESS(
  ADId int not null ,
  VER_ID int,
  AD_AGGREGATE VARCHAR2(50 BYTE)  
);

insert into employee values (500,12,100);
insert into employee values (501,13,101);
insert into employee values (502,22,102);
insert into employee values (503,23,103);

insert into ADDRESS values (100,12,'abc');
insert into ADDRESS values (101,13,'bc');
insert into ADDRESS values (102,22,,'def');
insert into ADDRESS values (103,-1,'ghi');

I wish to fetch all records by joining employee and Address along with the ver_id =-1 which means all eomployees with id 500 to 503 should appear in response as below 
currently it does not show Address record with 103 and ver_id=-1 I want that also should be part of response

select * from ADDR_INFO ai ,ADDRESS a
where ai.ADId=a.ADId ( )
        and ai.VER_ID=a.VER_ID ( );

Result set should look like below 
500 12  100     100 12 abc
501 13  101     101 13 bc
502 22  102     102 22 def
503 23  103     103 -1 ghi

I tried with or condition but its not working as below

select * from employee ai ,ADDRESS a
where ai.ADId=a.ADId ( )
        and (ai.VER_ID=a.VER_ID ( ) or a.VER_ID=-1 );

CodePudding user response：

If I understand correctly, you want to join address and employee to show all record with ver_id = -1. So you can can write this:

select a.empid,a.adid,a.ver_id,b.ad_aggregate  from employee a join  address b  on a.ADId=b.ADId;

Is it your mean?