Identify and delete None Matching Data (associations)

[pelle90]

Hi.

I have two tables, one with sales orders and one with product-information.

They are connected through a Product ID. However only some of the product IDs are involved in sale transactions. This means that there are some product IDS which exists in the Product-table but not in the sales table. I want to delete the products (entire row) for the products that haven´t been involved in a sale transaction.

So ending result will be a modified product table with only products that have at least one sales transaction. (and sales table will stay the same).

Any suggestions? Thanks in advance!

[HirisH_V7 · Accepted solution]

Some thing like this, using sample tables...

Sales:
LOAD * INLINE [
    Sales, Product_ID
    33, 1
    22, 2
];

Product:
LOAD * INLINE [
    Product_ID, Product_Name
    1, A
    2, B
    3, C
]where Exists(Product_ID,Product_ID);

//Or use inner join like..

Sales:
LOAD * INLINE [
    Sales, Product_ID
    33, 1
    22, 2
];

Inner Join


Product:
LOAD * INLINE [
    Product_ID, Product_Name
    1, A
    2, B
    3, C
];

You can use left join or keep with sales table as well. Follow, where exists for better performance.

HTH,

Hirish 

[pelle90]

Thank you!