CODEWITHSUNDEEP
sqloracle-database
ayscha
ayscha

Reputation: 189

Oracle sql. Compare two tables to find missing data

I have 2 tables:

Operator (id, name, product_id)
Product (id)

Operator table contains this data: 

| 1 | Op1 | Pr1 |  
| 2 | Op2 | Pr2 |

Product table: 

|    Pr1   |  
|    Pr2   |  
|    Pr3   |

And what i need is to show the products that Op1 does NOT have, and products that Op2 does NOT have: 

| Op1 | Pr2 |  
| Op1 | Pr3 |  
| Op2 | Pr1 |  
| Op2 | Pr3 |

Could you please advise an efficient way of solving this task. I have 1 solution with building cross join, but i think there should be a right and easier way.

Upvotes: 2

Views: 1498

Answers (3)

Robert Love
Robert Love

Reputation: 12581

A cross join is what is needed here.

select o.name, p.id from product p
cross join operator o
where 
  o.product_id != p.id

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I would use minus:

demo

select o.name op_name, p.name pr_name from operator o cross join product p
minus 
select name, product_id from operator

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't understand your data model. You seem to have a column called operator.name that is supposed to match product.id. Let me assume the data format is actually reasonable. You should really have three tables:

  • Operators -- with one row per "operator_id"
  • Products -- with one row per "product_id"
  • OperatorProducts -- a junction table between the two

You can do this in two steps. Start by creating all combinations of operator and product (cross join). Then weed out the ones that are present (left join):

select o.operator_id, p.product_id
from (select distinct id as operator_id from operator) o cross join
     product p left join
     operator op
     on op.id = o.operator_id and op.product_id = p.product_id
where p.product_id is null;

If you need the names, you can join them in.

Upvotes: 1

Related Questions