0
select firstName, lastName from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'Database Systems' 
in(select firstName, lastName 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'C++');``

I need to find the male students who have taken both Database Systems and C++, to do this I need to left join the tables students, registration, and courses,

New contributor
Fiona O is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • could you please post the complete error message? – nbk Nov 9 at 22:08
  • yes, just updated it – Fiona O Nov 9 at 22:12
0

Your in clause is missing some column try

select 
   firstName, lastName 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'Database Systems' 
and students.studentID
in (select studentID 
from students, courses, registration
where students.studentID = registration.studentID 
and courses.courseCode = registration.courseCode 
and gender = 'M' and courseName = 'C++');
  • This worked thank you so much!!!! – Fiona O Nov 9 at 22:17
  • don't forget to mark it as answered – nbk Nov 9 at 22:30
0

Your query fails because of the way you use the in operator, which requires a column name or an expression in the left side.

Based on your description of your goal, I suspect that your query could be rewritten to use exists condition with correlated subqueries for filtering, like so:

select 
    firstName, 
    lastName 
from students s
where 
    gender = 'M'
    and exists(
        select 1
        from courses c
        inner join registration r on c.courseCode = r.courseCode 
        where 
            c.courseName = 'Database Systems' 
            and s.studentID = r.studentID 
    )
    and exists(
        select 1
        from courses c
        inner join registration r on c.courseCode = r.courseCode 
        where 
            c.courseName = 'C++' 
            and s.studentID = r.studentID 
    )   

Another possible solution would be to use aggregation, with a having clause for filtering:

select s.firstName, s.lastName 
from students s
inner join registration r 
    on s.studentID = r.studentID 
inner join courses c 
    on  c.courseCode = r.courseCode 
    and c.courseName in ('Database Systems',  'C++' )
where s.gender = 'M'
group by s.studentID, s.firstName, s.lastName 
having count(distinct c.courseName) = 2
0

This query can be more easily written using a HAVING clause to check that the count of courses that the student has taken out of the set ('Database Systems', 'C++') is 2:

SELECT s.studentID, s.firstName, s.lastName 
FROM students s
JOIN registration r ON s.studentID = r.studentID 
JOIN courses c ON c.courseCode = r.courseCode 
WHERE s.gender = 'M' AND c.courseName IN ('Database Systems', 'C++')
GROUP BY s.studentID, s.firstName, s.lastName
HAVING COUNT(DISTINCT c.courseName) = 2

Note I have rewritten your JOINs in the preferred style with ON conditions.

Your Answer

Fiona O is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.