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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.