quote:

---

Originally posted by velliraj

Hi

I have two tables eg:
Table a

Empid salary
1 5000
1 2000
1 3000

table b

empid pone
1 12345
1 3456

i ned to get output as

empid salary phone
1 5000 12345
1 2000 3456
1 3000 -


please help

wheni try with left join it is returning the multiples. plz help.

---

SELECT A.ID, A.NUM , B.NUM FROM
(

SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY NUM DESC)as ROW_ID,* FROM #tempA ) as A
LEFT OUTER JOIN 
(
SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY NUM DESC)as ROW_ID,* FROM #tempB ) as B
ON  A.ROW_ID	=	B.ROW_ID

quote:

---

Originally posted by rajdaksha

Hi velliraj

Its not make sense. can you please check the table if u have any other unique keys.

Try this...its working(2005/2008)...but not suggested.

SELECT A.ID, A.NUM , B.NUM FROM
(

SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY NUM DESC)as ROW_ID,* FROM #tempA ) as A
LEFT OUTER JOIN 
(
SELECT DENSE_RANK() OVER (PARTITION BY ID ORDER BY NUM DESC)as ROW_ID,* FROM #tempB ) as B
ON  A.ROW_ID	=	B.ROW_ID

-------------------------
R...

---

quote:

---

Originally posted by rajdaksha

Hi

Yes so that i have mentioned like this...Try this...its working(2005/2008)...but not suggested.

-------------------------
R...

---

CREATE TABLE #temp1
(
	 
     Empid	INT NULL,
     salary INT NULL,
	 ID2	INT	
)
GO

CREATE TABLE #temp2
(
     Empid	INT NULL,
     phone  INT NULL,
	 ID2	INT 
)
GO

INSERT INTO #temp1(Empid,salary)
SELECT 1, 5000 UNION ALL
SELECT 1, 2000 UNION ALL
SELECT 1, 3000

INSERT INTO #temp2(Empid,phone)
SELECT 1, 12345 UNION ALL
SELECT 1 ,3456

SELECT * FROM #temp1
SELECT * FROM #temp2


DECLARE @LOOPINDEX INT

SET @LOOPINDEX = 0
UPDATE T
   SET ID2 = @LOOPINDEX,@LOOPINDEX=@LOOPINDEX + 1
FROM #temp1 T

SET @LOOPINDEX = 0
UPDATE T
   SET ID2 = @LOOPINDEX,@LOOPINDEX=@LOOPINDEX + 1
FROM #temp2 T

SELECT * FROM #temp1
SELECT * FROM #temp2

SELECT A.Empid, A.salary , B.phone FROM
(

SELECT * FROM #temp1 ) as A
LEFT OUTER JOIN 
(
SELECT * FROM #temp2 ) as B
ON  A.ID2	=	B.ID2


DROP TABLE #temp1
DROP TABLE #temp2