Join query for non matching records in oracle

-- 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
chokkaStudentAsked:
Who is Participating?
 
sdstuberCommented:
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);
0
 
sdstuberCommented:
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 )
0
 
slightwv (䄆 Netminder) 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
)
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chokkaStudentAuthor Commented:
Can i use Inner join for only matching ac_sys_nr
0
 
slightwv (䄆 Netminder) 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.
0
 
chokkaStudentAuthor Commented:
My question, if i need only match records,i can go for INNER JOIN QUERY.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
chokkaStudentAuthor Commented:
Yes, my plan is to put the inner join results to a temp table

and run a minus query
0
 
slightwv (䄆 Netminder) 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.
0
 
sdstuberCommented:
>>> 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)
0
 
slightwv (䄆 Netminder) 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.
0
 
chokkaStudentAuthor Commented:
Thanks !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.