compare two tables using full outer join to highlight missing record from table

Question

I have two tables.

The first table, tblBasket is shown below,

 Code   ProductCode    Price
 SLK    ABC            20
 SLK    DEF            30
 SLK    GHI            40

The second table, tblOrders is shown below,

Code   ProductCode    SaleId     Amount
SLK    ABC            MMM        20
SLK    DEF            MMM        30
SLK    GHI            MMM        40
SLK    ABC            XXX        20
SLK    DEF            XXX        30

I need to check the tblBasket against tblOrders. The logic is that the basket is sold as a whole i.e. all 3 products with the code SLK must be sold together.

In the table tblOrder is a list of orders from different sales people (SaleId). Each SaleId where it has the code SLK should have the same 3 product codes as the basket. So in the above example saleId XXX is missing the product GHI. I need a query to flag this.

I tried the query below but it only return me 5 records it doesn't show me the missing product from saleId XXX even though I'm using a full outer join.

select * from tblBasket b full outer join tblOrders o
on b.Code = o.Code and b.ProductCode = o.ProductCode

What is your question? What do you want the query to return? — Gordon Linoff, Nov 16 '15 at 14:49
All products appear in both tables. It will be in your result. You have incorrect statement copied or schema of tables are shown incorrectly. Which products is not in basket? Which basket is not in order? — Giorgi Nakeuri, Nov 16 '15 at 14:50
apologies. Just realised that what I was thinking makes no sense. Will delete this post. Apologies again — mHelpMe, Nov 16 '15 at 14:53
@mHelpMe . . . The question is basically ok. It just needs a question. It is unclear what you want returned. — Gordon Linoff, Nov 16 '15 at 14:56
@GordonLinoff I'm just going through your post now, think that's probably what I'm after. Like I say just checking now — mHelpMe, Nov 16 '15 at 14:57

score 0 · Answer 1 · answered Nov 16 '15 at 14:55

You can summarize the data to get the number of expected and matching products for each sale. Assuming that there are no duplicates in either table:

select sc.saleid, sc.code,
       count(*) as numProducts,
       count(o.productcode) as numMatchingProducts
from (select distinct saleid, code from tblOrders o) sc join
     tblBasket b
     on b.code = sc.code left join
     tblOrders o
     on o.saleid = sc.saleid and o.code = sc.code and
        b.productcode = o.productcode
group by sc.saleid, sc.code;

The logic is to generate the full combination of all products for each saleid. Then, the left join determines whether or not the products in the order match the expected products.

You can then add a having clause if you want to get some subset of orders -- such as the complete ones, or the incomplete ones, or the ones missing one product.

score -1 · Answer 2 · answered Nov 16 '15 at 14:55

-1

Do a left outer join from tblOrders to tblBaskets, joining on ProductCode. If you just want the orders where its missing a product, simply add a "WHERE tblBaskets.ID IS NULL"

answered Nov 16 '15 at 14:55

David P

2,027
3
15
27

compare two tables using full outer join to highlight missing record from table

2 Answers2