August 24, 2012 at 7:17 am
I have a table A:
StudID RollNo
Alpha D1234
beta A1122
charlie D1234
bravo C1342
tom B1964
harry A1122
and table B:
RollNo SubjectCode
D1234 1001
A1122 4001
D1234 2001
C1342 5001
B1964 6001
A1122 3001
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.
August 24, 2012 at 7:24 am
The same comment applies as from your last thread. There is no guaranteed row order in SQL tables, therefore you cannot ask for 'the first occurrence' unless you define an order explicitly.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2012 at 7:29 am
ganeshkumar005 (8/24/2012)
I have a table A:StudID RollNo
Alpha D1234
beta A1122
charlie D1234
bravo C1342
tom B1964
harry A1122
and table B:
RollNo SubjectCode
D1234 1001
A1122 4001
D1234 2001
C1342 5001
B1964 6001
A1122 3001
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.
August 24, 2012 at 7:31 am
both these posts looked way to much like homework;
I don't think the OP will learn anything if we do the work for him, without letting him learn the techniques.
Lowell
August 24, 2012 at 7:44 am
Lowell - there could be always some lateral learnings. You write a script, I see how to write it. I am not into T-SQl by the way. And thanks for your help, everybody.
August 24, 2012 at 7:47 am
You can do it using CTE also.
--========= Student table
IF OBJECT_ID('tempdb..#Student') IS NOT NULL
BEGIN
DROP TABLE #Student
END
GO
CREATE TABLE #Student
(
StudID VARCHAR(100),
RollNo VARCHAR(100)
)
GO
INSERT INTO #Student VALUES('Alpha', 'D1234')
INSERT INTO #Student VALUES('beta', 'A1122')
INSERT INTO #Student VALUES('charlie', 'D1234')
INSERT INTO #Student VALUES('bravo', 'C1342')
INSERT INTO #Student VALUES('tom', 'B1964')
INSERT INTO #Student VALUES('harry', 'A1122')
Go
--========= StudentSubjects table
IF OBJECT_ID('tempdb..#StudentSubjects') IS NOT NULL
BEGIN
DROP TABLE #StudentSubjects
END
GO
CREATE TABLE #StudentSubjects
(
RollNo VARCHAR(100),
SubjectCode VARCHAR(100),
)
GO
INSERT INTO #StudentSubjects VALUES ('D1234', 1001)
INSERT INTO #StudentSubjects VALUES ('A1122', 4001)
INSERT INTO #StudentSubjects VALUES ('D1234', 2001)
INSERT INTO #StudentSubjects VALUES ('C1342', 5001)
INSERT INTO #StudentSubjects VALUES ('B1964', 6001)
INSERT INTO #StudentSubjects VALUES ('A1122', 3001)
Go
--======= Solution:
;WITH MyCTE (RollNo, LowestSubjectCode)
AS
(
SELECT RollNo, MIN (SubjectCode) AS LowestSubjectCode FROM #StudentSubjects GROUP BY RollNo
)
SELECT S.StudId,S.RollNo,MC.LowestSubjectCode
FROM #Student S INNER JOIN MyCTE MC
ON S.RollNo = MC.RollNo
August 24, 2012 at 7:48 am
i've put more than a few posts on the board here on SSC, so I've helped an aweful lot of people.
;
the best way to learn is to try. if you posted a query that wasn't working, but showed effort, we could help you learn.
I'm really afraid you'll just take the solutions here, hand them in, but not understand them;
when the next question comes that is similar, you'll be stuck, because you didn't get a handle on hte original.
Lowell
August 27, 2012 at 1:35 am
Agree with you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply