Select Results based on Second Query

Question

Select Results based on Second Query

SLLRDK

Old Hand

Points: 384
More actions
April 16, 2010 at 7:55 am

#233979

Hi,
I have two queries the first shows all students that have attended a course:
SELECT DISTINCT tblStudent.intStudentId AS 'StudentID'
FROM tblSchool INNER JOIN
tblECourse INNER JOIN
tblCourse INNER JOIN
tblECourseBooking ON tblCourse.intCourseId = tblECourseBooking.intCourseId ON
tblECourse.intECourseId = tblECourseBooking.intECourseId INNER JOIN
tblECourseStatus ON tblECourse.intECourseStatusId = tblECourseStatus.intECourseStatusId INNER JOIN
tblStudent INNER JOIN
tblEnrol ON tblStudent.intStudentId = tblEnrol.intStudentId INNER JOIN
tblEnrolBookingStatus ON tblEnrol.intEnrolBookingStatusId = tblEnrolBookingStatus.intEnrolBookingStatusId ON
tblECourse.intEnrolId = tblEnrol.intEnrolId ON tblSchool.intSchoolId = tblECourse.intSchoolId LEFT OUTER JOIN
tblNationality ON tblStudent.intNationalityId = tblNationality.intNationalityId LEFT OUTER JOIN
tblGroup ON tblEnrol.intGroupId = tblGroup.intGroupId LEFT OUTER JOIN
tblCourseGroup ON tblCourse.intCourseGroupId = tblCourseGroup.intCourseGroupId
WHERE (tblECourseStatus.txtName = 'active')
AND (tblEnrol.intPositionId <> 1020 OR tblEnrol.intPositionId IS NULL)
AND (tblECourseBooking.intECourseBookingStatusId <> 200)
AND (tblEnrolBookingStatus.txtName = 'Booked')
AND (tblECourse.dteFromDate <= '09/04/09') -- Will be a variable date
AND (tblECourse.dteToDate >= '08/01/09') -- Will be a variable date
The second shows details for each student:
SELECT tblStudent.intStudentId AS 'StudentID',
tblStudent.txtTitle,
tblStudent.txtForename,
tblStudent.txtSurname,
tblNationality.txtName,
tblCourse.txtCode,
tblECourseBooking.dteFromDate,
tblECourseBooking.dteToDate,
tblEnrol.intDurationWeeksRounded,
tblCourse.decNumberOfHours * tblEnrol.intDurationWeeksRounded AS 'Hours'
FROM tblSchool INNER JOIN
tblECourse INNER JOIN
tblCourse INNER JOIN
tblECourseBooking ON tblCourse.intCourseId = tblECourseBooking.intCourseId ON
tblECourse.intECourseId = tblECourseBooking.intECourseId INNER JOIN
tblECourseStatus ON tblECourse.intECourseStatusId = tblECourseStatus.intECourseStatusId
INNER JOIN tblStudent INNER JOIN
tblEnrol ON tblStudent.intStudentId = tblEnrol.intStudentId INNER JOIN
tblEnrolBookingStatus ON tblEnrol.intEnrolBookingStatusId =
tblEnrolBookingStatus.intEnrolBookingStatusId ON
tblECourse.intEnrolId = tblEnrol.intEnrolId ON tblSchool.intSchoolId = tblECourse.intSchoolId
LEFT OUTER JOIN tblNationality ON tblStudent.intNationalityId = tblNationality.intNationalityId
LEFT OUTER JOIN tblGroup ON tblEnrol.intGroupId = tblGroup.intGroupId
LEFT OUTER JOIN tblCourseGroup ON tblCourse.intCourseGroupId = tblCourseGroup.intCourseGroupId
WHERE (tblECourseStatus.txtName = 'active')
AND (tblEnrol.intPositionId <> 1020 OR tblEnrol.intPositionId IS NULL)
AND (tblECourseBooking.intECourseBookingStatusId <> 200)
AND (tblEnrolBookingStatus.txtName = 'Booked')
What I want to do is show the students details if they appear in query A. Can anyone help advice what the best solution is? I was thinking a subquery would work but as of yet have not got it to display the correct information.
Students can attend more than one course so I want to see all course details that appear in Query B if the student appears in Query A.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Need a lot of patience to study your SMALL queries. but you can do one thing. put the result( same data ) of above queries on let say two temp tables and then ask your requirement. that will be more readable and understandable.:-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Not too happy with how you format your code, and your FROM clauses would take too long to reformat in a manner that would make sense to me.

Using your code, this is what I came up with fairly quickly:

with ActiveStudents as (

SELECT DISTINCT

tblStudent.intStudentId AS 'StudentID'

FROM

tblSchool INNER JOIN

tblECourse INNER JOIN

tblCourse INNER JOIN

tblECourseBooking ON tblCourse.intCourseId = tblECourseBooking.intCourseId ON

tblECourse.intECourseId = tblECourseBooking.intECourseId INNER JOIN

tblECourseStatus ON tblECourse.intECourseStatusId = tblECourseStatus.intECourseStatusId INNER JOIN

tblStudent INNER JOIN

tblEnrol ON tblStudent.intStudentId = tblEnrol.intStudentId INNER JOIN

tblEnrolBookingStatus ON tblEnrol.intEnrolBookingStatusId = tblEnrolBookingStatus.intEnrolBookingStatusId ON

tblECourse.intEnrolId = tblEnrol.intEnrolId ON tblSchool.intSchoolId = tblECourse.intSchoolId LEFT OUTER JOIN

tblNationality ON tblStudent.intNationalityId = tblNationality.intNationalityId LEFT OUTER JOIN

tblGroup ON tblEnrol.intGroupId = tblGroup.intGroupId LEFT OUTER JOIN

tblCourseGroup ON tblCourse.intCourseGroupId = tblCourseGroup.intCourseGroupId

WHERE

(tblECourseStatus.txtName = 'active')

AND (tblEnrol.intPositionId <> 1020 OR tblEnrol.intPositionId IS NULL)

AND (tblECourseBooking.intECourseBookingStatusId <> 200)

AND (tblEnrolBookingStatus.txtName = 'Booked')

AND (tblECourse.dteFromDate <= '09/04/09') -- Will be a variable date

AND (tblECourse.dteToDate >= '08/01/09') -- Will be a variable date

)

SELECT

tblStudent.intStudentId AS 'StudentID',

tblStudent.txtTitle,

tblStudent.txtForename,

tblStudent.txtSurname,

tblNationality.txtName,

tblCourse.txtCode,

tblECourseBooking.dteFromDate,

tblECourseBooking.dteToDate,

tblEnrol.intDurationWeeksRounded,

tblCourse.decNumberOfHours * tblEnrol.intDurationWeeksRounded AS 'Hours'

FROM

tblSchool INNER JOIN

tblECourse INNER JOIN

tblCourse INNER JOIN

tblECourseBooking ON tblCourse.intCourseId = tblECourseBooking.intCourseId ON

tblECourse.intECourseId = tblECourseBooking.intECourseId INNER JOIN

tblECourseStatus ON tblECourse.intECourseStatusId = tblECourseStatus.intECourseStatusId

INNER JOIN tblStudent INNER JOIN

tblEnrol ON tblStudent.intStudentId = tblEnrol.intStudentId INNER JOIN

tblEnrolBookingStatus ON tblEnrol.intEnrolBookingStatusId =

tblEnrolBookingStatus.intEnrolBookingStatusId ON

tblECourse.intEnrolId = tblEnrol.intEnrolId ON tblSchool.intSchoolId = tblECourse.intSchoolId

LEFT OUTER JOIN tblNationality ON tblStudent.intNationalityId = tblNationality.intNationalityId

LEFT OUTER JOIN tblGroup ON tblEnrol.intGroupId = tblGroup.intGroupId

LEFT OUTER JOIN tblCourseGroup ON tblCourse.intCourseGroupId = tblCourseGroup.intCourseGroupId

WHERE

(tblECourseStatus.txtName = 'active')

AND (tblEnrol.intPositionId <> 1020 OR tblEnrol.intPositionId IS NULL)

AND (tblECourseBooking.intECourseBookingStatusId <> 200)

AND (tblEnrolBookingStatus.txtName = 'Booked')

AND EXISTS(SELECT 1 FROM ActiveStudents ast where ast.StudentID = tblStudent.StudentID) -- Could join this in the from clause, but you FROM clause is too confusing for a quick change.

;