We help IT Professionals succeed at work.

Join query for non matching records in oracle

chokka
chokka asked
on
-- From these two tables, I need all records from tacphas for which ac_sys_nr is not available in tactosr

select * from TACPHAS
where cny_cd = 'CA'
and psl_job_cgy_elg_cd = '2'
and dat_cx_tm_prd_nr = 0

/
-- COMMON COLUMN :  ac_sys_nr

select * from tactosr
where sales_vew_cd = 'S13CA'
and dat_cx_tm_prd_nr = 0
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
select * from TACPHAS
where cny_cd = 'CA'
and psl_job_cgy_elg_cd = '2'
and dat_cx_tm_prd_nr = 0
and ac_sys_nr not in ( select ac_sys_nr from tactosr
where sales_vew_cd = 'S13CA'
and dat_cx_tm_prd_nr = 0 )
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Not sure I completely understand the query.

Sample data and expected results would help a lot.

Maybe a NOT IN query?


select * from TACPHAS
 where cny_cd = 'CA'
 and psl_job_cgy_elg_cd = '2'
 and dat_cx_tm_prd_nr = 0
and ac_sys_nr not in (
 select ac_sys_nr from tactosr
 where sales_vew_cd = 'S13CA'
 and dat_cx_tm_prd_nr = 0
)
/
Most Valuable Expert 2011
Top Expert 2012
Commented:
the not in assumes ac_sys_nr is NOT NULL in either table.

If it might be then use NOT EXISTS instead


SELECT *
  FROM tacphas
 WHERE cny_cd = 'CA'
   AND psl_job_cgy_elg_cd = '2'
   AND dat_cx_tm_prd_nr = 0
   AND NOT EXISTS
           (SELECT NULL
              FROM tactosr
             WHERE sales_vew_cd = 'S13CA'
               AND dat_cx_tm_prd_nr = 0
               AND tacphas.ac_sys_nr = tactosr.ac_sys_nr);
chokkaStudent

Author

Commented:
Can i use Inner join for only matching ac_sys_nr
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>for only matching ac_sys_nr

But the original question was 'doesn't exist in the other table'.  I don't see how you can join where records don't match or don't exist.
chokkaStudent

Author

Commented:
My question, if i need only match records,i can go for INNER JOIN QUERY.
Mark GeerlingsDatabase Administrator

Commented:
An "inner join" (a simple join in traditional Oracle syntax) will give you only those records that do have a matching record in the other table.   But, that seems to be the opposite of what you asked for: "I need all records from tacphas for which ac_sys_nr is not available in tactosr".

The "not exists" suggestion from sdstuber looks like the best option to me, based on my understanding of your question.
chokkaStudent

Author

Commented:
Yes, my plan is to put the inner join results to a temp table

and run a minus query
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>if i need only match records,i can go for INNER JOIN QUERY.

Probably.  Maybe an OUTER join.  It all depends on what data you want.  We cannot answer that for you since we don't have your data nor do we know your expected results.

We can only go by what you ask for.
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> My question, if i need only match records,i can go for INNER JOIN QUERY.

yes

but your original question is for NOT  MATCHING

so use the NOT IN or NOT EXISTS (they are functionally different, pick the one that matches what you are trying to do)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Yes, my plan is to put the inner join results to a temp table ...  and run a minus query

Why?  Sounds like a LOT of extra work when there appear to be alternatives.
chokkaStudent

Author

Commented:
Thanks !!