ganeshkumar005 (8/24/2012)
I have a table A:StudIDRollNo
AlphaD1234
betaA1122
charlieD1234
bravoC1342
tomB1964
harryA1122
and table B:
RollNoSubjectCode
D12341001
A11224001
D12342001
C13425001
B19646001
A11223001
I want to join the table A and B. For studID Alpha from table A i want the subjectcode 1001 only from table B and not want what other subjectcodes as you can see 'D1234' is repeated in table B.
I'm going to assume you want the lowest SubjectCode.
--== SAMPLE DATA
SELECT StudID, RollNo
INTO #tableA
FROM (VALUES('Alpha','D1234'),('beta','A1122'),('charlie','D1234'),('bravo','C1342'),
('tom','B1964'),('harry','A1122'))a(StudID, RollNo);
--== SAMPLE DATA
SELECT RollNo, SubjectCode
INTO #tableB
FROM (VALUES('D1234',1001),('A1122',4001),('D1234',2001),('C1342',5001),('B1964',6001),
('A1122',3001))a(RollNo, SubjectCode);
--== ACTUAL SOLUTION
SELECT tblA.StudID, tblA.RollNo, subQuery.SubjectCode
FROM #tableA tblA
CROSS APPLY (SELECT TOP 1 RollNo, SubjectCode
FROM #tableB tblB
WHERE tblA.RollNo = tblB.RollNo) subQuery;
Also, take a look at this link about SQL Server ordering.