Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Union

Author

Topic

AJCherniak
Starting Member

7 Posts

Posted - 2010-12-10 : 13:42:44

How do I combine these two tables so that all columns and rows from each show up? I'm expecting the resulting output to be 5 columns (based on the header names after the AS command).

Query 1:

SELECT 

FSE_HistoryInventoryMove.TransactionDate AS 'AdjDate', 
FSE_HistoryInventoryMove.InventoryQuantity*-1 AS 'AdjQty', 
FSE_HistoryInventoryMove.AvailabilityDate AS 'UseBy', 
FSE_HistoryInventoryMove.LotNumberFrom AS 'Lot#'

FROM BIReports.dbo.FSE_HistoryInventoryMove FSE_HistoryInventoryMove

WHERE (FSE_HistoryInventoryMove.ItemNumber='600128') AND (FSE_HistoryInventoryMove.BinFrom='v10259')

UNION

SELECT 

FSE_HistoryInventoryMove.TransactionDate AS 'AdjDate', 
FSE_HistoryInventoryMove.InventoryQuantity AS 'AdjQty', 
FSE_HistoryInventoryMove.AvailabilityDate AS 'UseBy', 
FSE_HistoryInventoryMove.LotNumberTo AS 'Lot#'

FROM BIReports.dbo.FSE_HistoryInventoryMove FSE_HistoryInventoryMove

WHERE (FSE_HistoryInventoryMove.ItemNumber='600128') AND (FSE_HistoryInventoryMove.BinTo='v10259')

Query 2:

SELECT FSE_HistoryInventoryAdjustment.TransactionDate AS 'AdjDate',
AdjQty = CASE FSE_HistoryInventoryAdjustment.ActionCode
WHEN '-' THEN (FSE_HistoryInventoryAdjustment.AdjustmentQuantity*-1) WHEN '+' THEN (FSE_HistoryInventoryAdjustment.AdjustmentQuantity) END, 
FSE_LotTrace.AvailabilityDate AS 'UseBy',
FSE_HistoryInventoryAdjustment.LotNumber AS 'Lot#',
FSE_HistoryInventoryAdjustment.Remark

FROM BIReports.dbo.FSE_HistoryInventoryAdjustment FSE_HistoryInventoryAdjustment, BIReports.dbo.FSE_LotTrace FSE_LotTrace

WHERE FSE_LotTrace.LotNumber = FSE_HistoryInventoryAdjustment.LotNumber AND ((FSE_HistoryInventoryAdjustment.ItemNumber='600128') AND (FSE_HistoryInventoryAdjustment.Bin='v10259'))

I then want to sort everything by the header "AdjDate".

Thanks in advance for the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 14:34:24

[code]SELECT * FROM
(
SELECT

FSE_HistoryInventoryMove.TransactionDate AS 'AdjDate',
FSE_HistoryInventoryMove.InventoryQuantity*-1 AS 'AdjQty',
FSE_HistoryInventoryMove.AvailabilityDate AS 'UseBy',
FSE_HistoryInventoryMove.LotNumberFrom AS 'Lot#',
NULL as Remark

FROM BIReports.dbo.FSE_HistoryInventoryMove FSE_HistoryInventoryMove

WHERE (FSE_HistoryInventoryMove.ItemNumber='600128') AND (FSE_HistoryInventoryMove.BinFrom='v10259')

UNION

SELECT

FSE_HistoryInventoryMove.TransactionDate AS 'AdjDate',
FSE_HistoryInventoryMove.InventoryQuantity AS 'AdjQty',
FSE_HistoryInventoryMove.AvailabilityDate AS 'UseBy',
FSE_HistoryInventoryMove.LotNumberTo AS 'Lot#',
NULL as Remark

FROM BIReports.dbo.FSE_HistoryInventoryMove FSE_HistoryInventoryMove

WHERE (FSE_HistoryInventoryMove.ItemNumber='600128') AND (FSE_HistoryInventoryMove.BinTo='v10259')

UNION ALL

SELECT FSE_HistoryInventoryAdjustment.TransactionDate AS 'AdjDate',
AdjQty = CASE FSE_HistoryInventoryAdjustment.ActionCode
WHEN '-' THEN (FSE_HistoryInventoryAdjustment.AdjustmentQuantity*-1) WHEN '+' THEN (FSE_HistoryInventoryAdjustment.AdjustmentQuantity) END,
FSE_LotTrace.AvailabilityDate AS 'UseBy',
FSE_HistoryInventoryAdjustment.LotNumber AS 'Lot#',
FSE_HistoryInventoryAdjustment.Remark

FROM BIReports.dbo.FSE_HistoryInventoryAdjustment FSE_HistoryInventoryAdjustment, BIReports.dbo.FSE_LotTrace FSE_LotTrace

WHERE FSE_LotTrace.LotNumber = FSE_HistoryInventoryAdjustment.LotNumber AND ((FSE_HistoryInventoryAdjustment.ItemNumber='600128') AND (FSE_HistoryInventoryAdjustment.Bin='v10259'))
)dt
ORDER BY AdjDate[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

AJCherniak
Starting Member

7 Posts

Posted - 2010-12-10 : 14:43:42

That worked perfectly. I do however have two additional select statements to add to this that have about 10 fields each. I guess I'd like to understand how this works. There were two date fields in each select statement (UseBy and AdjDate). How does it know which is which when combining the select statements? Is it looking at the header name that I created? Or is it looking at the original field name from the database, which only happen to be the same in the example I submitted? Thanks for the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 14:52:27

The colum names (aliases, I mean AS AdjDate) of the very first query in a UNION construct are the column names for the whole query.
Then, as you can see I've made it a derived table (select...)dt
Now you have a "table" named dt and the column names of that table are coming from the ver first select inside...

Clear to you?

And please consider: union selects all need the same count and datatype of columns, hence i've added the NULL as Remark to the first two queries.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 14:57:22

And also consider: UNION gives DISTINCT rows and UNION ALL gives all rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

AJCherniak
Starting Member

7 Posts

Posted - 2010-12-10 : 16:09:27

I think I understand. Thank you for the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts