Advanced SQL question, joining to queries

oh SQL Query Gurus,

I’ve past my SQL knowledge and googling ability…

I have 2 SQL queries (see below); one which is StockStatus (all parts in stock) and one which is parts with STK-* Transactions. StockStatus gives me 4,610 rows, and STK-* gives me 4200 rows.

I want to join the 2 to return all of the data from STK-* and those parts that show up in the StockStatus but are not in the STK-*.

If I paste the data from queries into excel (Stk-* on top) and run the find duplicates it will give me that data I want, but I’m hoping to do it automatically.

(StockStatus)

	SELECT DISTINCT 
		pb.PartNum, LEFT(p2.PartDescription, 25) PartDesc
		, p.TypeCode, pb.WarehouseCode, pb.BinNum, pb.OnhandQty
		, pt.TranDate as '1stTranDate', DatePart(year, pt.TranDate) as '1stYear' 
		, '01/01/1900' as LastTransDate
		, 9999 as DaysNotUsed, 1900 as 'LastYear'
		, (pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost 
				+ pc.AvgMtlBurCost + pc.AvgSubContCost) AvgUnitCost
		, ((pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost 
				+ pc.AvgMtlBurCost + pc.AvgSubContCost) * pb.OnhandQty) TotalCost
	FROM Erp.PartBin pb
		JOIN Erp.PartTran pt 
			ON pb.Company = pt.Company AND pb.PartNum = pt.PartNum
		JOIN Erp.PartCost pc
			ON pb.Company = pc.Company AND pb.PartNum = pc.PartNum
		INNER JOIN Erp.WhseBin wb
			ON pb.Company = wb.Company AND pb.WarehouseCode = wb.WarehouseCode 
										AND pb.BinNum = wb.BinNum 
		INNER JOIN Erp.Part p
			ON pb.Company = p.Company AND pb.PartNum = p.PartNum
	WHERE
		wb.NonNettable = 0
		AND pb.WarehouseCode IN ('ANT', 'COAX', 'HOWM', 'LAB', 'MACH', 'MPA'
						 ,'OVSHOP', 'PACK', 'RADOME', 'RAWSTK', 'SHVM', 'WELD')
		AND pt.TranDate = 
				(SELECT MIN(PartTran.TranDate) 
				FROM Erp.PartTran 
				WHERE pt.Company = PartTran.Company 
						AND pt.PartNum = PartTran.PartNum)

(STK-*)

	SELECT DISTINCT 
		pb2.PartNum, LEFT(p2.PartDescription, 25) PartDesc
		, p2.TypeCode, pb2.WarehouseCode, pb2.BinNum, pb2.OnhandQty
		,'00/00/0000' as '1stTransDate', 0000 as '1stYear'
		, pt2.TranDate as LastTransDate
		, DATEDIFF(day, pt2.TranDate, Convert(date, GETDATE())) DaysNotUsed
		, DatePart(year, pt2.TranDate) as 'Year'
		, (pc2.AvgBurdenCost + pc2.AvgLaborCost + pc2.AvgMaterialCost 
			+ pc2.AvgMtlBurCost + pc2.AvgSubContCost) AvgUnitCost
		, ((pc2.AvgBurdenCost + pc2.AvgLaborCost + pc2.AvgMaterialCost 
			+ pc2.AvgMtlBurCost + pc2.AvgSubContCost) * pb2.OnhandQty) TotalCost
	FROM Erp.PartBin pb2
		JOIN Erp.PartTran pt2 
			ON pb2.Company = pt2.Company AND pb2.PartNum = pt2.PartNum
		JOIN Erp.PartCost pc2
			ON pb2.Company = pc2.Company AND pb2.PartNum = pc2.PartNum
		INNER JOIN Erp.WhseBin wb2
			ON pb2.Company = wb2.Company AND pb2.WarehouseCode = wb2.WarehouseCode 
				AND pb2.BinNum = wb2.BinNum 
		INNER JOIN Erp.Part p2
			ON pb2.Company = p2.Company AND pb2.PartNum = p2.PartNum
	WHERE
		wb2.NonNettable = 0
		AND pt2.TranType IN ('STK-ASM', 'STK-MTL', 'STK-CUS', 'STK-KIT') 
		AND pb2.WarehouseCode IN ('ANT', 'COAX', 'HOWM', 'LAB', 'MACH', 'MPA', 
							'OVSHOP' , 'PACK', 'RADOME', 'RAWSTK', 'SHVM', 'WELD')
		AND pt2.TranDate = 
				(SELECT MAX(PartTran.TranDate) 
				FROM Erp.PartTran 
				WHERE PartTran.TranType IN 
						('STK-ASM', 'STK-MTL', 'STK-CUS', 'STK-KIT') 
					AND pt2.Company = PartTran.Company 
					AND pt2.PartNum = PartTran.PartNum)

You want to use Union or UnionAll.

Union will remove duplicate rows.

UnionAll will not.

Duplicate rows meaning all of the SELECT fields need to match? There are 5
fields what the values will differ…

Thanks,

Norman Hutchins
System Administrator
Howell Laboratories, Inc.

You can do a left join of the two queries, StockStatus first.Then, for each field, check with isnull to give the appropriate field. Something like:

select isnull(SP.PartNum, SS.PartNum) PartNum, isnull(SP.PartDesc,SS.PartDesc), …
from (StockStatus query here) SS left join (STK-* query here) SP
on SS.PartNum = SP.PartNum and SS.WarehouseCode = SP.WarehouseCode

I think you want to join on part and warehouse, but try it out.

1 Like

Here are the two most important things about Union:

  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.

With Union, you don’t need the Distinct, so I would take those out. You can play around with Union and Union All to see which one gives you what you are expecting. You can always throw the Distinct back in later if you want to see what happens. I’m not going to redo your whole code, just the last line of your first query and the first line of your second:

AND pt.PartNum = PartTran.PartNum)
Union
SELECT pb2.PartNum, LEFT(p2.PartDescription, 25) PartDesc

That won’t do what he needs, regardless of union or union all, he’ll end up with over 8000 rows.

@pmarques - you are correct, I misread the OP.

@nhutchins - How do you want to show the data? are you saying you want row per part and the columns from both queries? As an example, let’s say the first query returns 5 columns and the second returns 5 columns; do you want 10 columns? Or do you want 5 columns with the data from the second query unless it is null and then you want the data from the first query?

I think you need to provide a better explanation so that we can help you.

@nhutchins Maybe try using an outer apply and checking where null like this:

SELECT DISTINCT 
	pb.PartNum, LEFT(p.PartDescription, 25) PartDesc
	, p.TypeCode, pb.WarehouseCode, pb.BinNum, pb.OnhandQty
	, pt.TranDate as '1stTranDate', DatePart(year, pt.TranDate) as '1stYear' 
	, '01/01/1900' as LastTransDate
	, 9999 as DaysNotUsed, 1900 as 'LastYear'
	, (pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost 
			+ pc.AvgMtlBurCost + pc.AvgSubContCost) AvgUnitCost
	, ((pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost 
			+ pc.AvgMtlBurCost + pc.AvgSubContCost) * pb.OnhandQty) TotalCost
FROM Erp.PartBin pb WITH (NOLOCK)
	JOIN Erp.PartTran pt WITH (NOLOCK)
		ON pb.Company = pt.Company AND pb.PartNum = pt.PartNum
	JOIN Erp.PartCost pc WITH (NOLOCK)
		ON pb.Company = pc.Company AND pb.PartNum = pc.PartNum
	INNER JOIN Erp.WhseBin wb WITH (NOLOCK)
		ON pb.Company = wb.Company AND pb.WarehouseCode = wb.WarehouseCode 
									AND pb.BinNum = wb.BinNum 
	INNER JOIN Erp.Part p WITH (NOLOCK)
		ON pb.Company = p.Company AND pb.PartNum = p.PartNum
	OUTER APPLY (SELECT TOP 1 pt2.PartNum FROM Erp.PartTran pt2 WHERE pt2.PartNum = p.PartNum AND pt2.Company = p.Company AND pt2.WareHouseCode = pb.WarehouseCode
	             AND pt2.TranType IN ('STK-ASM', 'STK-MTL', 'STK-CUS', 'STK-KIT')) bad
WHERE
	wb.NonNettable = 0
	AND pb.WarehouseCode IN ('ANT', 'COAX', 'HOWM', 'LAB', 'MACH', 'MPA'
					 ,'OVSHOP', 'PACK', 'RADOME', 'RAWSTK', 'SHVM', 'WELD')
	AND pt.TranDate = 
			(SELECT MIN(PartTran.TranDate) 
			FROM Erp.PartTran 
			WHERE pt.Company = PartTran.Company 
					AND pt.PartNum = PartTran.PartNum)
   AND bad.PartNum is NULL