CODEWITHSUNDEEP
sqlsql-servert-sql
Troy Taylor
Troy Taylor

Reputation: 11

Joining Data Between 2 SQL Tables

Okay, I'm not as SQL savvy as I should be, but I'm familiar with how Joins, Unions, etc... work and I just can't come up with a solution for this. I'm trying to get data from two different tables, but the monkey wrench is that the right side table may have more rows than I actually want in my result set. The only criteria I have to join the two tables is an email address. Here's the code:

CREATE TABLE #PPEInfo(StudentEmail nvarchar(128), StudentName nvarchar(128), DevUnits int)

INSERT INTO #PPEInfo (StudentEmail, DevUnits)
SELECT e.StudentEmail AS Email, sum(ck.DevelopmentUnits) AS DevUnits
FROM Enrollments e, CourseKeys ck
WHERE e.CertGenerated = 'true'
AND e.CourseId = ck.CourseId
GROUP BY e.StudentEmail
ORDER BY DevUnits DESC

SELECT p.StudentEmail, p.DevUnits, s.StudentName  
FROM #PPEInfo p
RIGHT OUTER JOIN Surveys s
ON p.StudentEmail = s.StudentEmail
ORDER BY DevUnits DESC, StudentName ASC

DROP TABLE #PPEInfo

The problem is that I receive multiple student names because they may not have used the same one when doing their submissions. For instance:

Email Address   James R. Salvati
Email Address   James Salvati

The only solution that I've come up with is to populate my temp table with the email addresses first and then query the Surveys table for the name using "TOP(1)" to get only one student name. It does work, but it's very CPU intensive, and I'm dealing with a lot of records. Here's the code (although I didn't care about the DevUnits at this point was just trying to come up with something):

CREATE TABLE #PPEInfo(ID int IDENTITY(1,1), StudentEmail nvarchar(128), StudentName nvarchar(128), DevUnits int)

INSERT INTO #PPEInfo (StudentEmail)
SELECT DISTINCT StudentEmail FROM Enrollments
WHERE CertGenerated = 'true'

DECLARE @rowID int
DECLARE @email nvarchar(128)

SET @rowID = (SELECT max(ID) FROM #PPEInfo)

WHILE (@rowID > 0)
BEGIN
    SET @email = (SELECT StudentEmail FROM #PPEInfo WHERE ID = @rowID)
    UPDATE #PPEInfo
    SET StudentName = (SELECT TOP(1) s.StudentName FROM Surveys s
                       WHERE s.StudentEmail = @email)
    WHERE ID = @rowID
    SET @rowID = @rowID - 1
END

SELECT * FROM #PPEInfo
ORDER BY DevUnits DESC

DROP TABLE #PPEInfo

I've never had to actually post on one of these boards. I usually find the solution or figure one out, but this one is just beyond my SQL prowess.

Thanks!!!

Upvotes: 1

Views: 77

Answers (2)

Héctor Luaces Novo
Héctor Luaces Novo

Reputation: 429

Correct me if I'm wrong, but I'm pretty sure that you can do this to achieve what you want:

select * from enrollments
inner join coursekeys on 
enrollments.studentemail = coursekeys.studentemail 
where coursekeys.studentname=(
    select top 1 studentname from coursekeys 
    where studentemail=enrollments.studentemail
);

I don't have access to a SQL Server at the moment, but I've succesfully achieved this on a MySQL server with similar tables to yours.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85126

It depends on how you want to determine which name to select when there are multiple. One posible way is below:

SELECT p.StudentEmail, p.DevUnits, MAX(s.StudentName)
FROM #PPEInfo p
RIGHT OUTER JOIN Surveys s
ON p.StudentEmail = s.StudentEmail
ORDER BY DevUnits DESC, StudentName ASC
GROUP BY p.studentEmail, p.devUnits

Here you are grouping by email and units and grabbing the "MAX" student name.

Also in your first query you should stop using implicit joins.

Upvotes: 1

Related Questions