horizon22 horizon22 - 7 months ago 8
SQL Question

How can I select a column from a NOT-IN sub-query

Here is a query that I need help with:

SELECT s.lastfirst, s.student_number, s.grade_level
FROM students s
WHERE s.schoolid = 300 AND s.enroll_status = 0 AND s.id not in
(
SELECT d.studentid
FROM section_meeting em INNER JOIN sections e
ON em.sectionid = e.id
INNER JOIN cc d ON e.id = d.sectionid
WHERE em.schoolid = 300 and em.period_number in (P1)
)


This query works fine except that I need to show the column em.period_number in the results as well. Can anyone suggest how to go about it?

Thanks!

Answer

query works fine exept that I need to show the column em.period_number in the results as well

If you have successfully got the result using your question query let me add period_number with join query..try :

SELECT       s.lastfirst, s.student_number, s.grade_level, section_meeting.period_number
FROM         students  s INNER JOIN
             section_meeting ON s.schoolid = section_meeting.schoolid
WHERE        (s.schoolid = 300) AND (s.enroll_status = 0) AND (s.id NOT IN
             (SELECT        d.studentid
             FROM         section_meeting  em INNER JOIN
                          sections  e ON em.sectionid = e.id INNER JOIN
                          cc  d ON e.id = d.sectionid
                          WHERE        (em.schoolid = 300) AND (em.period_number IN (P1))))