August 24, 2012 at 7:17 am
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.
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 24, 2012 at 7:29 am
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.
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
)
SELECTS.StudId,S.RollNo,MC.LowestSubjectCode
FROM#Student S INNER JOIN MyCTE MC
ONS.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