Oracle - Count distinct value without duplication from 2 tables


I would like to count the number of time an equipment type was used on loads from 2 different tables, without any duplication that could be in both.

Each table as a load id and an equipment type attach to it. We cannot have the same load ID with 2 different equipment type.


In load table, i may have below loads as an example:
Load table
In FP_MASTER_HIST, i may have below loads as an example:
As you can see. LOAD2 is duplicated and should only count as One.
What we also see is that we have same load id's in both table: load2 and load4. This also should be count only Once.

So the final result i would like to have in this Query would look like this:
Query result
I'm also giving a sample of the tables including the final result.

How can i do that?

Thank you for your help.
LVL 11
Who is Participating?

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

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.

awking00Information Technology SpecialistCommented:
select equipment_type_id, count(*) as countwithoutduplication from
(select id, equipment_type_id from load
 select id, equipment_type_id from fp_master_hist)
group by equipment_type_id
order by equipment_type_id;

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
Wilder1626Author Commented:
Thank you. This is exactly what i needed.
awking00Information Technology SpecialistCommented:
Glad to help :-)
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.