Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to do this? Part-2 Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 7:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 11, 2014 4:48 PM
Points: 84, Visits: 169
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.
Post #1349640
Posted Friday, August 24, 2012 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:31 AM
Points: 5,149, Visits: 11,991
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1349649
Posted Friday, August 24, 2012 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:20 AM
Points: 2,379, Visits: 7,578
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1349657
Posted Friday, August 24, 2012 7:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 12,905, Visits: 32,157
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349659
Posted Friday, August 24, 2012 7:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 11, 2014 4:48 PM
Points: 84, Visits: 169
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.
Post #1349670
Posted Friday, August 24, 2012 7:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:10 AM
Points: 1,325, Visits: 2,590
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




Sujeet Singh
Post #1349672
Posted Friday, August 24, 2012 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 12,905, Visits: 32,157
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349673
Posted Monday, August 27, 2012 1:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 11, 2014 4:48 PM
Points: 84, Visits: 169
Agree with you.
Post #1350237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse