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

t-sql 2008 join temp table to other table Expand / Collapse
Author
Message
Posted Saturday, June 7, 2014 2:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:17 AM
Points: 362, Visits: 422
In 2008 t-sql I have the sql listed below where I obtain results by calling a stored procedure [test].[dbo].[sroom]. I am joining the results from calling a stored procedure with an existing table called Transactions. I am joining the 2 tables by the value by the SchoolNumber and the school.

The problem is that sometimes there is more than one row returned by the stored procedure by the studentNumber. The student number is not a unique key. The problem is that more than one studentNumber data row can be returned by the query below. My problem is that I only want 1 row for each studentnumber.

I have tried to select top 1 for the sql listed below with no success. Thus can you show me modifed sql listed below where only one row for each unique studentNumber in the temp table called #hResults is joined to the test.dbo.Transactions table? The user does not care what row is returned by studentNumber they just want one row returned.

IF OBJECT_ID('tempdb..#hResults') IS NOT NULL
DROP table studentNumber

CREATE TABLE #hResults(
personID INT,
studentNumber VARCHAR(15),
firstName VARCHAR(35),
middleName VARCHAR(30),
lastName VARCHAR(40),
suffix VARCHAR(50),
fullName VARCHAR(108),
enrollmentID INT,
structureID INT,
grade VARCHAR(4),
calendarID INT,
calendarName VARCHAR(30),
endYear SMALLINT,
schoolNumber VARCHAR(7),
schoolName VARCHAR(40),
courseID INT,
courseNumber VARCHAR(13),
courseName VARCHAR(30),
sectionID INT,
sectionNumber SMALLINT,
homeroomSection BIT,
teacherDisplay VARCHAR(30),
teacherPersonID INT)

INSERT #hResults
EXEC [test].[dbo].[sroom] @endYear, @calendarID, N'C'


SELECT s.SchoolNumber,
s.SchoolName,
homeroom.courseNumber ,
homeroom.sectionNumber AS Section,
homeroom.teacherDisplay as Teacher,
f.StudentNumber,
replace(StudentLastName,'"','') + ' ' + replace(StudentFirstName,'"','')
as 'Student Name'
,TransactionAmount
,CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' +
substring (TransactionPaymentDate,1,2)+ '-' +
substring(TransactionPaymentDate,3,2)) as TransactionPaymentDate
from test.dbo.Transactions f
INNER JOIN
test.dbo.Schools s
on rtrim(ltrim(s.SchoolNumber)) =rtrim(ltrim( f.SchoolNumber))
LEFT JOIN #hResults homeroom
ON homeroom.studentNumber = f.StudentNumber
WHERE rtrim(ltrim(s.SchoolNumber)) = rtrim(ltrim(@SchoolNumber))
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as
Date)
>= @StartDate
AND
Cast(Right(TransactionPaymentDate,4)+Left(TransactionPaymentDate,4) as Date)
<= @EndDate
group by s.SchoolNumber,
s.SchoolName,
homeroom.courseNumber,
homeroom.sectionNumber,
homeroom.roomName,
homeroom.teacherDisplay,
f.StudentNumber,
replace(StudentLastName,'"','') + ' ' +
replace(StudentFirstName,'"','')
,TransactionAmount
,TransactionPaymentDate
,TransactionPaymenttime
,PaymentType
,EventNumber
homeroom.courseNumber,
homeroom.sectionNumber,
homeroom.roomName,
homeroom.teacherDisplay,
StudentNumber,
TransactionPaymentDate

IF OBJECT_ID('tempdb..#hResults') IS NOT NULL
DROP table #hResults

Thus can you show me modifed sql listed above where only one row for each unique studentNumber in the temp table called #hResults is joined to the test.dbo.Transactions table?
Post #1578570
Posted Saturday, June 7, 2014 9:58 AM This worked for the OP Answer marked as solution


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 23,062, Visits: 31,589
Not really sure what you are looking for here since we can't see what you see.

It would help if you could post the DDL for the tables involved (including index definitions), sample data for the tables (INSERT statements), and the expected results based on the sample data. By seeing what you are working with and what you are expecting in return we can help you get the code you need and have it tested as well.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1578579
Posted Monday, June 9, 2014 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:17 AM
Points: 362, Visits: 422
I will give the dat structures next time.
Post #1578856
Posted Monday, June 9, 2014 11:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 389, Visits: 2,306
the temp table #hResults is returning multiple rows for each student number, and for each student number, you're making use of distinct values of homeroom.courseNumber, sectionNumber, roomName, teacherDisplay, etc...

Once you choose to get only one row for each student from #hResults, you'll have to choose _which_ courseNumber, sectionNumber, roomName, teacherDisplay etc for each respective and distinct student number in #hResults.

Is this whats needed?
Post #1578916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse