select distinct from 3 tables

[poe]

i have 3 tables:

1. inventory (sku, whse, desc, sohqty)

2. sell (sku, whse, desc, sellqty)

3. po (sku, whse, desc, poqty)

 

inventory : each record is unique

po & sell : may have records with duplicating lines

 

the search is by sku & whse

 

how do i get DISTINCT sku_whse from all 3 tables in one sql statement

 

 

as you can see in the :

 

1. inventory table - by concatenating sku & whse, all records are unique

 

2. sell table - sku 1_whse c1 is listed multiple times (ie to 3 different customers)

and sku 12_whse c1 is also sold to multiple customers AND it is not listed on either inventory table or po table

 

3. po table - sku 13_whse c1 is also listed multiple records and is also NOT included in the inventory table or sell table

 

 

 

 

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 1, 'C1', 'DESC 1', 10 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 2, 'C1', 'DESC 2', 15 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 3, 'C1', 'DESC 3', 20 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 4, 'C1', 'DESC 4', 25 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 5, 'C1', 'DESC 5', 30 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 6, 'C1', 'DESC 6', 35 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 7, 'C1', 'DESC 7', 40 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 8, 'C1', 'DESC 8', 45 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 9, 'C1', 'DESC 9', 50 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 10, 'C1', 'DESC 10', 45 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 4, 'X1', 'DESC 4', 60 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 5, 'X1', 'DESC 5', 75 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 6, 'X1', 'DESC 6', 80 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 7, 'X1', 'DESC 7', 85 );

INSERT INTO `INVENTORY` ( `SKU`, `WHSE`, `DESC`, `SOHQTY` ) values ( 8, 'X1', 'DESC 8', 90 );

 

 

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 1, 'C1', 'DESC 1', 8 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 1, 'C1', 'DESC 1', 9 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 1, 'C1', 'DESC 1', 7 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 2, 'C1', 'DESC 2', 4 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 2, 'C1', 'DESC 2', 5 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 2, 'C1', 'DESC 2', 1 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 5, 'C1', 'DESC 5', 2 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 5, 'C1', 'DESC 5', 5 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 5, 'C1', 'DESC 5', 4 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 6, 'C1', 'DESC 6', 6 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 6, 'C1', 'DESC 6', 8 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 9, 'C1', 'DESC 9', 9 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 12, 'C1', 'DESC 12', 5 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 12, 'C1', 'DESC 12', 7 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 15, 'C1', 'DESC 15', 8 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 15, 'C1', 'DESC 15', 2 );

INSERT INTO `SELL` ( `SKU`, `WHSE`, `DESC`, `SELLQTY` ) values ( 15, 'C1', 'DESC 15', 3 );

 

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 1, 'C1', 'DESC 1', 25 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 4, 'C1', 'DESC 4', 15 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 4, 'C1', 'DESC 4', 10 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 6, 'C1', 'DESC 6', 17 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 6, 'C1', 'DESC 6', 8 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 6, 'C1', 'DESC 6', 23 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 11, 'C1', 'DESC 11', 30 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 12, 'C1', 'DESC 12', 25 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 13, 'C1', 'DESC 13', 28 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 13, 'C1', 'DESC 13', 15 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 14, 'C1', 'DESC 14', 17 );

INSERT INTO `PO` ( `SKU`, `WHSE`, `DESC`, `POQTY` ) values ( 15, 'C1', 'DESC 15', 26 );

 

 

my ultimate goal is to list all unique sku_whse with desc, and 3 qty columns sum(sohqty) sum(sellqty) sum(poqty)

 

so the result will look like :

 

 

  SKU_WHSE    DESC            SOHQTY    SELLQTY    POQTY 

 

1_C1DESC110 2425

2_C1DESC215100

4_C1DESC425 025

5_C1DESC530 110

5_X1DESC575 00

6_C1DESC635 1448

6_X1DESC680 00

7_C1DESC740 00

7_X1DESC78500

8_C1DESC84500

8_X1DESC89000

9_C1DESC95090

10_C1DESC104500

11_C1DESC110030

12_C1DESC1201225

13_C1DESC130043

14_C1DESC140017

15_C1DESC1501326

[kickstart]

Hi

 

Will any sku / whse on sell and po always be on inventory?

 

Are the descriptions the same for a sku / whse on all 3 tables? If so why duplicate them, and if now how do you decide which one to use?

 

All the best

 

Keith

[poe]

Hi Keith,

 

Will any sku / whse on sell and po always be on inventory?

 

NO, a sku/whse could be on just 1, 2 or all 3, but not always on inventory,

 

YES the description for an exact sku/whse is the same everywhere.

 

Thanks

 

Chris.

 

I tried playing around with UNION, but cant seem to get it to group & sum the qty. - maybe UNION isnt the way to go ?? 

[kickstart]

Hi

 

Think I would suggest using UNION ALL:-

 

SELECT sku, whse, SUM(Quantity)
FROM (SELECT sku, whse, sohqty As Quantity
FROM INVENTORY
UNION ALL
SELECT sku, whse, sellqty As Quantity
FROM sell 
UNION ALL
SELECT sku, whse, poqty As Quantity
FROM po )
GROUP BY sku, whse

 

However not sure how you know with description you want to pick up.

 

All the best

 

Keith

[poe]

Keith, this is working pretty good !

 

thanks

 

chris

[poe]

add-on...

 

if i wanted to pull the description from a completly different table.

 

how / where would i put in a LEFT JOIN ?

 

 

i tried... but it isnt working

SELECT sku, whse, SUM(Quantity)

FROM (SELECT sku, whse, sohqty As Quantity

FROM INVENTORY

UNION ALL

SELECT sku, whse, sellqty As Quantity

FROM sell

UNION ALL

SELECT sku, whse, poqty As Quantity

FROM po )

LEFT JOIN PRODUCTS ON INVENTORY.SKU = PRODUCTS.SKU

GROUP BY sku, whse

[kickstart]

Hi

 

Think so, but join not on a particular tables sku:-

 

SELECT a.sku, a.whse, a.TotQty, b.Desc
FROM (SELECT sku, whse, SUM(Quantity) AS TotQty
FROM (SELECT sku, whse, sohqty As Quantity
FROM INVENTORY
UNION ALL
SELECT sku, whse, sellqty As Quantity
FROM sell
UNION ALL
SELECT sku, whse, poqty As Quantity
FROM po ) InvSellPo 
GROUP BY sku, whse) a
LEFT JOIN PRODUCTS b ON a.SKU = b.SKU

 

All the best

 

Keith