Join issue - 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 2005 Forums

Transact-SQL (2005)

Join issue

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-07 : 20:56:11

I have two table looks like below


Table A

Id      object_key
1001       111
1002       189
1003       1456

Table B


ID    Reason_cd    labor_id   object_key
1001    1           2           98
1001    5           11          111
1002    1           34          189
1003    1           45          1234
1003    2           12          1237
1003    5           43          1345
1003    5           23          1456

I am trying to join TAble A and B using object_key and ID..
but if the matching object_key and ID in table B have reson_cd 5 then .. I need to look for the latest record (by object_key) where reason_Cd <> 5


 
ID         A.object_key      B.ObjecT_key     reason_cd   labor_id  
1001        111                 98                1          2
1002        189                 189               1          34
1003        1456                1237              2          12

How can I do join conditionally like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 04:51:28

[code]DECLARE @TableA table
(
Id int,
object_key int
)
INSERT INTO @TableA
SELECT 1001 , 111
UNION ALL
SELECT 1002 , 189
UNION ALL
SELECT 1003, 1456

DECLARE @TableB table
(
ID int, Reason_cd int,labor_id int, object_key int
)

INSERT INTO @TableB
SELECT 1001, 1, 2, 98
UNION ALL
SELECT 1001, 5, 11 , 111
UNION ALL
SELECT 1002, 1 , 34, 189
UNION ALL
SELECT 1003, 1, 45, 1234
UNION ALL
SELECT 1003, 2, 12, 1237
UNION ALL
SELECT 1003, 5, 43, 1345
UNION ALL
SELECT 1003, 5, 23, 1456

SELECT a.ID, a.object_key,COALESCE(b.object_key,tmp.object_key),COALESCE(b.Reason_cd,tmp.Reason_cd),COALESCE(b.labor_id,tmp.labor_id)
FROM @TableA a
LEFT JOIN @TableB b
ON a.Id =b.ID
AND a.object_key=b.object_key
AND b.Reason_cd <>5
OUTER APPLY (SELECT b1.* FROM
@TableB b1
INNER JOIN (SELECT ID,MAX(object_key) AS object_key
FROM @TableB
WHERE Reason_cd<>5
GROUP BY ID)t
ON t.ID=b1.ID
AND t.object_key = b1.Object_key
WHERE b1.ID=a.ID)tmp

Output
------------------------------------

ID object_key Bobject_key Reason_cd labor_id
----------- ----------- ----------- ----------- -----------
1001 111 98 1 2
1002 189 189 1 34
1003 1456 1237 2 12
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:44:05

Using same sample data as Visakh

;WITH Yak (ID, Object_Key, RecID, Reason_CD, Labor_ID)
AS (
	SELECT	ID,
		Object_Key,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Object_Key DESC),
		Reason_CD,
		Labor_ID
	FROM	@TableB
	WHERE	Reason_CD <> 5
)

SELECT		y.ID,
		t.Object_Key,
		y.Object_Key,
		y.Reason_CD,
		y.Labor_ID
FROM		Yak AS y
INNER JOIN	@TableA AS t ON t.ID = y.ID
WHERE		RecID = 1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 05:45:26

And if you prefer not using CTE

SELECT		y.ID,
		t.Object_Key,
		y.Object_Key,
		y.Reason_CD,
		y.Labor_ID
FROM		(
			SELECT	ID,
				Object_Key,
				ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Object_Key DESC) AS RecID,
				Reason_CD,
				Labor_ID
			FROM	@TableB
			WHERE	Reason_CD <> 5
		) AS y
INNER JOIN	@TableA AS t ON t.ID = y.ID
WHERE		RecID = 1

E 12°55'05.25"
N 56°04'39.16"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-08 : 08:46:21

Thank you very much Peso and visakh16

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-08 : 13:17:11

quote:

Originally posted by Peso

Using same sample data as Visakh

;WITH Yak (ID, Object_Key, RecID, Reason_CD, Labor_ID)
AS (
	SELECT	ID,
		Object_Key,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Object_Key DESC),
		Reason_CD,
		Labor_ID
	FROM	@TableB
	WHERE	Reason_CD <> 5
)

SELECT		y.ID,
		t.Object_Key,
		y.Object_Key,
		y.Reason_CD,
		y.Labor_ID
FROM		Yak AS y
INNER JOIN	@TableA AS t ON t.ID = y.ID
WHERE		RecID = 1

E 12°55'05.25"
N 56°04'39.16"

peso -- problem is i still would like to join by objecT_key if the matching record ( join by object_key) from table B is not 5 ( it can be 1,2,3,4)..

You code only show the latest mathcing record from table b where reason_cd <> 5..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 13:28:12

quote:
Originally posted by jung1975

quote:
Originally posted by Peso

Using same sample data as Visakh
;WITH Yak (ID, Object_Key, RecID, Reason_CD, Labor_ID)
AS (
SELECT ID,
Object_Key,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Object_Key DESC),
Reason_CD,
Labor_ID
FROM @TableB
WHERE Reason_CD <> 5
)

SELECT y.ID,
t.Object_Key,
y.Object_Key,
y.Reason_CD,
y.Labor_ID
FROM Yak AS y
INNER JOIN @TableA AS t ON t.ID = y.ID
WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

peso -- problem is i still would like to join by objecT_key if the matching record ( join by object_key) from table B is not 5 ( it can be 1,2,3,4)..

You code only show the latest mathcing record from table b where reason_cd <> 5..

Thats reason why i gave the LEFT JOIN to take record with matching Object id if one exists or use the OUTER APPLY to return record with same ID and max objectid if it cant find a matching objectid.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-08 : 15:30:42

The OUTER APPLY method seems a bit better on the test data, but for fun I did it without the OUTER APPLY as an alternate method:

SELECT
	A.ID,
	A.object_key AS AObjectKey,
	COALESCE(B.object_key, C.object_key) AS BObjectKey,
	COALESCE(B.Reason_cd, C.Reason_cd) AS Reason_cd,
	COALESCE(B.labor_id, C.labor_id) AS labor_id
FROM
	@TableA AS A
LEFT OUTER JOIN
	@TableB AS B
	ON A.ID = B.ID
	AND A.object_key = B.object_key
	AND Reason_cd <> 5
LEFT OUTER JOIN
	(
		SELECT 
			B.ID, 
			B.object_key,
			B.Reason_cd,
			B.labor_id,
			ROW_NUMBER() OVER(PARTITION BY ID ORDER BY object_key DESC) AS RowNum
		FROM 
			@TableB AS B
		WHERE 
			Reason_cd <> 5
	) AS C
	ON A.ID = C.ID
	AND	C.RowNum = 1

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-08 : 16:47:46

quote:

Originally posted by Lamprey

The OUTER APPLY method seems a bit better on the test data, but for fun I did it without the OUTER APPLY as an alternate method:

SELECT
	A.ID,
	A.object_key AS AObjectKey,
	COALESCE(B.object_key, C.object_key) AS BObjectKey,
	COALESCE(B.Reason_cd, C.Reason_cd) AS Reason_cd,
	COALESCE(B.labor_id, C.labor_id) AS labor_id
FROM
	@TableA AS A
LEFT OUTER JOIN
	@TableB AS B
	ON A.ID = B.ID
	AND A.object_key = B.object_key
	AND Reason_cd <> 5
LEFT OUTER JOIN
	(
		SELECT 
			B.ID, 
			B.object_key,
			B.Reason_cd,
			B.labor_id,
			ROW_NUMBER() OVER(PARTITION BY ID ORDER BY object_key DESC) AS RowNum
		FROM 
			@TableB AS B
		WHERE 
			Reason_cd <> 5
	) AS C
	ON A.ID = C.ID
	AND	C.RowNum = 1

Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources