join query -- incorrect values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join query -- incorrect values

Author

Topic

shriroshanraj
Starting Member

30 Posts

Posted - 2013-08-01 : 07:51:34

select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded,a.pointspossible,a.duedatetime,asg.submissiondatetime
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2 and a.gradebook=1

if i execute this i get 8 records

select testname,totalpoints from test where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

if i execute this i get 3 records

i joined these two queries
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded,a.pointspossible,a.duedatetime,asg.submissiondatetime,a.testname,a.totalpoints
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
inner join test t
on t.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2 and a.gradebook=1

if i execute this i get 24 records, but i need to get 8 records

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 08:13:39

You have to either group by the columns in the first query, or pick one row out of the multiple rows from each group. If you want to group by , you need to decide what you want to do for the multiple values from test table. For example, you might decide that you want the max of the testname and sum of the totalpoints. If so, do like this:

SELECT  p.firstname ,
        p.lastname ,
        a.assignmentname ,
        asg.pointsawarded ,
        a.pointspossible ,
        a.duedatetime ,
        asg.submissiondatetime ,
        MAX(t.testname) AS testname ,
        SUM(t.totalpoints) AS totalpoints
FROM    person2 p
        INNER JOIN coursesectionroster cs ON cs.personid = p.personid
        INNER JOIN assignment a ON a.coursesectionid = cs.coursesectionid
        INNER JOIN test t ON t.coursesectionid = cs.coursesectionid
        LEFT JOIN assignmentsubmission asg ON a.assignmentid = asg.assignmentid
WHERE   cs.coursesectionid = 'b78a6efe-ac77-4e49-806a-fc2fad71068b'
        AND cs.courserole = 2
        AND a.gradebook = 1
GROUP BY
		p.firstname ,
        p.lastname ,
        a.assignmentname ,
        asg.pointsawarded ,
        a.pointspossible ,
        a.duedatetime ,
        asg.submissiondatetime

But this is only an example. Depending on the logic you want to implement, you might have to do something different.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources