SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join tables on sorted values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Starting Member

USA
45 Posts

Posted - 11/19/2013 :  13:27:14  Show Profile  Reply with Quote
Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?

In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC
order by M.LOC_CODE

LOCATION ITEM LOT QTY
01-01-A 100 abc 25
01-02-A NULL NULL NULL
01-03-A 200 def 50
01-03-A 200 ghi 50

My problem is adding the third counted inventory table because it could look like anything depending on what we find in the racks:
LOCATION ITEM LOT QTY
01-01-A 100 abc 25 (exact match)
01-02-A 150 cba 75 (Item found)
01-03-A 200 ghi 50 (LOT swapped)
01-03-A 300 def 50 (Item Changed)

My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:
select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN
E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE
order by M.LOC_CODE

LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 def 50 01-03-A 300 def 50
01-03-A 200 ghi 50 01-03-A 200 ghi 50
01-03-A 200 ghi 50 01-03-A 300 def 50


I'd like to just end up with 2 lines sorted by location, item, lot, qty so I can see that there is a problem with that location.
LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 ghi 50 01-03-A 300 def 50

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 11/19/2013 :  14:33:01  Show Profile  Reply with Quote
I didn't quite follow your example after the first sentence ("s there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?"), but based on that, the short answer is "no".

You would have to do one of two things:
1. find another column on which you can join
2. create (perhaps on the fly) another column that you can join on.

An example of creating another column would be as follows. I am not saying that this is the right thing to do in your case, I am just showing an example:
;WITH A AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY MASTER_LOC ORDER BY (SELECT 1)) AS RN -- creating another column
	FROM M_LOC
),
b AS 
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY INVT_LOC ORDER BY (SELECT 1)) AS RN -- creating another column
	FROM C_INVT
)
SELECT
	a.MASTER_LOC,
	b.[AS] AS ORIG_ITEM,
	b.ORIG_LOT,
	b.ORIG_QTY
FROM
	a LEFT JOIN b ON a.MASTER_LOC = b.INV_LOC
		AND a.RN = b.RN -- additional join condition
ORDER BY
	a.LOC_CODE;
Go to Top of Page

ITTrucker
Starting Member

USA
45 Posts

Posted - 11/20/2013 :  09:04:10  Show Profile  Reply with Quote
Working down the row_number() path and it's going to work. I had to add a coalesce on the row_number join because some rows not matching and I added a third join in there but I think that's it's pulling in the data that we want.

WITH LOC AS
(
SELECT STUFF
),
INVT AS
(
SELECT ROW_NUMBER() PARTITION AND ORDER, STUFF
),
COUNT AS
(
SELECT ROW_NUMBER() SAME PARTITION AND ORDER, STUFF
)

SELECT *
FROM
LOC M LEFT OUTER JOIN
INVT I ON M.LOC_CODE = I.LOC_CODE left OUTER JOIN
COUNT C ON M.LOC_CODE = C.LOC_CODE AND coalesce(I.ROW_NUM,1) = coalesce(C.ROW_NUM,1)

Just need to throw it into a report and only show the ones where everything doesn't match, and I'm all set.

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000