Join tables on sorted values - 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

General SQL Server Forums

New to SQL Server Programming

Join tables on sorted values

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2013-11-19 : 13:27:14

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-19 : 14:33:01

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;

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2013-11-20 : 09:04:10

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources