SQL Join using 2 fields
Hi
I am trying to join a [StockandWarehouse] table to a [StockTrans] table. I need to join on the stock number as well as the warehouse code e.g.
StockandWarehouse StockTrans
Stock1 , Whse(NSW) Stock1 , Whse(NSW)
Stock1 , Whse(QLD) Stock1 , Whse(NSW)
Stock1 , Whse(VIC)
Stock1 , Whse(WA)
In this example, the result needs to pick up 4 lines:
- Stock1 , Whse(NSW)
- Stock1 , Whse(QLD)
- Stock1 , Whse(VIC)
- Stock1 , Whse(WA)
I am new to this and so have been struggling with the code to get this right. I seem to either end up with just the matching 2, or with everything. Any help would be appreciated.
Thank you
Joanne
Views: 45
Replies to This Discussion
-
Permalink Reply by Brynn Furmidge on -
Your SQL code will need to look something like this:
StockTrans
LEFT JOIN StockandWarehouse ON (StockTrans.StockCode = StockandWarehouse.StockCode) AND (StockTrans.WhseCode = StockandWarehouse.WhseCode)
-
Permalink Reply by Joanne McCoy on -
This is the first thing I tried, but it results in only the exact matches being returned. So in the example above I only get the Whse(NSW) and Whse(QLD) transactions. I need the other two as well.
My StockandWarehouse file is the key file. If I only join on the StockCode then I get the following:
If a stock item has stock in 3 warehouses and there are 7 stock transactions, then I get 21 transactions. In Excel the Sales amount is repeated 3 times for every stock transaction. I have tried working around this by matching Warehouse Code in the sales amount, but this doesn't work where there are sales but no stock in the warehouse (the warehouse codes don't match).
Any other ideas please?
Follow Alchemex on twitter
Like Alchemex on facebook
© 2012 Created by Admin.
