CODEWITHSUNDEEP
sqloracle-database
user2093576
user2093576

Reputation: 3092

AND and OR in oracle sql

I need to write something like:

select * 
from HR_EMP_TRANSFER t1, HR_EMP_PROFILE_MSTR_TT t2 
where (t1.APPROVAL_STATUS='P' and t1.EMPNO = '13724')
   OR (t2.APPROVAL_STATUS='P' and t2.EMPNO = '13724')

but currently it is not giving me the results only for empno 13724

Both the tables have different table structure. But these two columns are same. I just need to combine these two queries some how and check if these are returning any value

select * from HR_EMP_TRANSFER t1 where t1.APPROVAL_STATUS='P' and t1.EMPNO = '13724'

 select * from HR_EMP_PROFILE_MSTR_TT t2 where t2.APPROVAL_STATUS='P' and t2.EMPNO = '13724'

Upvotes: 0

Views: 76

Answers (3)

Chaitanya Kotha
Chaitanya Kotha

Reputation: 476

You can achieve this by using SQL Join.

select * from HR_EMP_TRANSFER t1 
 inner join HR_EMP_PROFILE_MSTR_TT t2 on t1.APPROVAL_STATUS = t2.APPROVAL_STATUS and t1.EMPNO = t2.EMPNO
where t1.EMPNO = 13724 and t1.APPROVAL_STATUS='P'

Upvotes: 0

MT0
MT0

Reputation: 167774

You could use a FULL OUTER JOIN:

SELECT * 
FROM   HR_EMP_TRANSFER t1
       FULL OUTER JOIN
       HR_EMP_PROFILE_MSTR_TT t2
       ON (    t1.APPROVAL_STATUS = t2.APPROVAL_STATUS
           AND t1.EMPNO = t2.EMPNO )
WHERE (t1.APPROVAL_STATUS='P' and t1.EMPNO = '13724')
   OR (t2.APPROVAL_STATUS='P' and t2.EMPNO = '13724')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You might want UNION ALL:

SELECT t1.*
FROM HR_EMP_TRANSFER t1
WHERE t1.APPROVAL_STATUS='P' and t1.EMPNO = '13724'
UNION ALL
SELECT t2.*
FROM HR_EMP_PROFILE_MSTR_TT t2 
WHERE t2.APPROVAL_STATUS='P' and t2.EMPNO = '13724';

This assumes that the two tables have the same structure (same columns, in the same order, with the same types). It returns the records in separate rows, rather than in one row.

Upvotes: 0

Related Questions