How to do this? Part-2

Question

How to do this? Part-2

Ganeshkumar005

Ten Centuries

Points: 1140
More actions
August 24, 2012 at 7:17 am

#263341

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

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.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 4

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.

Divine Flame SSCoach Points: 15943 More actions · Answer 5

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

Sujeet Singh

Lowell SSC Guru Points: 323518 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 7

Ganeshkumar005

Ten Centuries

Points: 1140

August 27, 2012 at 1:35 am

#1530217

Agree with you.