DROP TABLE test_login_mst_1 

DROP TABLE test_lcr_details_1 

DROP TABLE test_user_company_1 

DROP TABLE test_company_det_1 

CREATE TABLE TEST_LOGIN_MST_1  
(	PK_TB_LOGIN_MASTER NUMBER(10,0),  
USER_ID VARCHAR2(50 CHAR) 
)  

CREATE TABLE TEST_LCR_DETAILS_1  
(	LINK_ID VARCHAR2(35 CHAR),  
FK_TB_LOGIN_MASTER NUMBER(10,0),  
FK_TB_COMPANY_DETAILS NUMBER(10,0) 
) 

CREATE TABLE TEST_USER_COMPANY_1  
(	FK_TB_COMPANY_DETAILS NUMBER(10,0),  
FK_TB_LOGIN_MASTER NUMBER(10,0) 
)  

CREATE TABLE TEST_COMPANY_DET_1  
(	PK_TB_COMPANY_DETAILS NUMBER(10,0) 
) 

Insert into TEST_LOGIN_MST_1 (PK_TB_LOGIN_MASTER,USER_ID) values (929733,'USER_001')

Insert into TEST_LOGIN_MST_1 (PK_TB_LOGIN_MASTER,USER_ID) values (929744,'USER_002')

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13603)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13605)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13747)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929733,13751)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13605)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13747)

Insert into TEST_LCR_DETAILS_1 (LINK_ID,FK_TB_LOGIN_MASTER,FK_TB_COMPANY_DETAILS) values ('DUMMY_LINK',929744,13751)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13603,929733)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13605,929733)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13747,929733)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13751,929733)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13603,929744)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13605,929744)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13747,929744)

Insert into TEST_USER_COMPANY_1 (FK_TB_COMPANY_DETAILS,FK_TB_LOGIN_MASTER) values (13751,929744)

Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13603)

Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13605)

Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13747)

Insert into TEST_COMPANY_DET_1 (PK_TB_COMPANY_DETAILS) values (13751)

commit

Select Uc.FK_TB_COMPANY_DETAILS 
From test_user_company_1 Uc, test_company_det_1 cd  
WHERE Uc.Fk_Tb_Login_Master in (select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')  
And Uc.Fk_Tb_Company_Details = Cd.Pk_Tb_Company_Details

select link_id, fk_tb_login_master, fk_tb_company_details 
from test_lcr_details_1 
where link_id='DUMMY_LINK'  
and fk_tb_login_master=(select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')  
and fk_tb_company_details IN (Select Uc.FK_TB_COMPANY_DETAILS 
From test_user_company_1 Uc, test_company_det_1 cd  
WHERE Uc.Fk_Tb_Login_Master in (select pk_Tb_Login_Master from test_login_mst_1 where user_id = 'USER_002')  
And Uc.Fk_Tb_Company_Details = Cd.Pk_Tb_Company_Details)