I have a couple of tables that I am trying to join together and I get duplicates, which I understand.
I am importing information (staging) that I use to add a PO to the shipping table (ship). Both tables have the same shipping number that I am joining on. I also use the quantities from both tables to determine which record the PO goes to.
If the quantities are the same, it doesn't matter which record the PO goes on as long as each record has a different PO on it.
I have 6 rows in each file and expect to get 6 rows back but I get 14. If there are no duplicate quantities it work correctly.
Here is the schema and query.
The results are:
Anyone know how I could change this query to get only 6 rows with the POs in the right place.
Thanks,
Tom
I am importing information (staging) that I use to add a PO to the shipping table (ship). Both tables have the same shipping number that I am joining on. I also use the quantities from both tables to determine which record the PO goes to.
If the quantities are the same, it doesn't matter which record the PO goes on as long as each record has a different PO on it.
I have 6 rows in each file and expect to get 6 rows back but I get 14. If there are no duplicate quantities it work correctly.
Here is the schema and query.
Code:
If OBJECT_ID('tempdb..#Stage') IS NOT NULL
DROP TABLE #Stage
If OBJECT_ID('tempdb..#Ship') IS NOT NULL
DROP TABLE #Ship
CREATE TABLE #Stage
(
StageID int,
ShipNumber varchar(10),
PONumber varchar(10),
iQty int
)
CREATE TABLE #Ship
(
ShipID int,
ShipmentNumber varchar(10),
Units int
)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (20, 'MZ12345', 'A4735', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (22, 'MZ12345', 'B7236', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (23, 'MZ12345', 'L4432', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (35, 'MZ12345', 'J3321', 40)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (36, 'MZ12345', 'U8911', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (32, 'MZ12345', 'V3199', 10)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (250, 'MZ12345', 40)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (251, 'MZ12345', 10)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (252, 'MZ12345', 25)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (253, 'MZ12345', 25)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (255, 'MZ12345', 10)
Insert #Ship (ShipID, ShipmentNumber, Units) VALUES (300, 'MZ12345', 10)
SELECT StageID, ShipID, PONumber, iQty, Units
FROM #Stage st
JOIN #Ship s
ON st.ShipNumber = s.ShipmentNumber
AND st.iQty = s.Units
The results are:
Code:
StageID ShipID PONumber iQty Units
35 250 J3321 40 40
20 251 A4735 10 10
23 251 L4432 10 10
32 251 V3199 10 10
22 252 B7236 25 25
36 252 U8911 25 25
22 253 B7236 25 25
36 253 U8911 25 25
20 255 A4735 10 10
23 255 L4432 10 10
32 255 V3199 10 10
20 300 A4735 10 10
23 300 L4432 10 10
32 300 V3199 10 10
Anyone know how I could change this query to get only 6 rows with the POs in the right place.
Thanks,
Tom