Fetch only those data which are common in both the query in Oracle

Question

I have 2 set's of query in which I want to join and display only those RJ_SPAN_ID which are common in both the query.

Here are both the query:-

SELECT  TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,
TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
FROM NE.MV_SPAN@NE
WHERE 
LENGTH(TRIM(RJ_SPAN_ID)) = 21
AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
 AND INVENTORY_STATUS_CODE = 'IPL'
 AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;

-- Query 2

select rj_span_id, rj_maintenance_zone_code from ne.mv_transmedia@ne
  where inventory_Status_code = 'IPL'
  and LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
  'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;

please suggest how to get it.

Answer 1

As you already have both queries, a simple option is to intersect what they return, i.e.

select ... from ... where ...   --> your 1st query
INTERSECT
select ... from ... where ...   --> your 2nd query

Applied to your case:

SELECT  TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,
        TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
FROM NE.MV_SPAN@NE
WHERE LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND INVENTORY_STATUS_CODE = 'IPL'
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
--
INTERSECT
--
select rj_span_id, rj_maintenance_zone_code 
from ne.mv_transmedia@ne
where inventory_Status_code = 'IPL'
  and LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;