SQL : join more than one table

Question

Write an sql query to select the names of the students who have attended all the lectures of professor 'JOHN' but not any lecture of professor 'JOSEPH'.

I have written this query, but it is giving me the name of student who are taking the lecture from joseph also.

select distinct s.studentname 
from Student s,Attendance a,Lecture l 
where s.StudentId=a.studid 
  and l.lecId = a.lectureid 
  and l.professor='JOHN' 
  and l.professor != 'JOSEPH';

I am making some error in above query but not able to identify the same.

here is the table structure

Student table

# StudentId, StudentName, Sex
'1', 'AMY', 'M'
'2', 'JACK', 'M'
'3', 'TONY', 'M'
'4', 'TARA', 'M'
'5', 'SARAH', 'F'
'6', 'TOM', 'F'

Lecture Table

# LecId, Subject, Professor
1, MATH, JOHN
2, MATH, JOSEPH
3, PHY, MARK
4, PHY, MAX
5, PHY, JOHN
6, CHEM, JOHN
7, CHEM, JOSEPH
8, HISTORY, JOSEPH

Attendance table

# StudId, LectureId
'1', '1'
'1', '2'
'3', '1'
'2', '5'
'2', '6'
'3', '4'
'1', '6'
'4', '5'
'5', '1'
'5', '2'
'1', '3'
'1', '4'
'1', '5'
'1', '6'

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 21 '20 at 09:26
You are using a join syntax that was made redundant in standard SQL in 1992. That was before MySQL was even invented! If you are attending a class or using a book or tutorial teaching you to use this ancient syntax, you should quit it. Use proper explicit joins instead (`INNER JOIN`, `LEFT OUTER JOIN`, etc.). — Thorsten Kettner, Jun 21 '20 at 10:12

GMB · Answer 1 · 2020-06-21T09:52:59.257

You need to look across all rows that belong to a given student, so a simple where clause cannot do what you want. Instead, you can use aggregation and filer with a having clause:

select s.studentname 
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid 
group by s.studentId, s.studentname 
having max(l.professor= 'JOHN') = 1 and max(l.professor = 'JOSEPH') = 0

If you want teachers that addented all lectures of John and none of Joseph, then:

select s.studentname 
from student s
inner join attendance a on s.studentId = a.studid
inner join lecture l on l.lecId = a.lectureid 
group by s.studentId, s.studentname 
having 
    sum(l.professor= 'JOHN') = (select count(*) from lecture where professor = 'JOHN')
    and max(l.professor = 'JOSEPH') = 0

hey sorry, made a little change in question which will impact a lot .. extremely sorry , could you please help me now :) — sparsh610, Jun 21 '20 at 09:24

Michał Turczyn · Answer 2 · 2020-06-21T13:32:28.827

0

Try:

select * from Student st
where exists(select * from Lecture l
             join Attendance a
             on l.LecId = a.LectureId
             where l.Professor = 'John'
             and a.StudId = st.StudentId)
and exists(select * from Lecture l
               join Attendance a
               on l.LecId = a.LectureId
               where l.Professor = 'Joseph'
               and a.StudId = st.StudentId)

edited Jun 21 '20 at 13:32

answered Jun 21 '20 at 09:15

Michał Turczyn

32,028
14
47
69

@sparsh610 Could you please add DDL so I can test it? Also, Tony also fulfilles requirements. What is your current result? – Michał Turczyn Jun 21 '20 at 09:17
how to get that in mysql ? – sparsh610 Jun 21 '20 at 09:18
Just put data in http://sqlfiddle.com and share the link – Michał Turczyn Jun 21 '20 at 09:20
hey sorry, made a little change in question which will impact a lot .. extremely sorry , could you please help me now :) – sparsh610 Jun 21 '20 at 09:24
@sparsh610 Try now. – Michał Turczyn Jun 21 '20 at 13:32

mkRabbani · Answer 3 · 2020-06-21T10:05:13.210

0

If you MySQL version support GROUP_CONCAT, you can try this below script-

Demo Here

SELECT S.StudentId,S.StudentName, GROUP_CONCAT(DISTINCT L.Professor)
FROM Attendance A
INNER JOIN Lecture L ON A.LectureId = L.LecId
INNER JOIN Student S ON A.StudId = S.StudentId
GROUP BY S.StudentId,S.StudentName
HAVING  GROUP_CONCAT(DISTINCT L.Professor) = 'JOHN'

This above query will return student list that only taking course from 'JOHN'. No one else.

But if your requirement changed to add more professors in the list, you can add Professors name accordingly in the HAVING clause checking. But you need to apply a appropriate ordering that case.

edited Jun 21 '20 at 10:05

answered Jun 21 '20 at 09:46

mkRabbani

16,295
2
15
24

close enogh, but why it not giving the name of JACK, studid : 2 , he is also attending only JOHN – sparsh610 Jun 21 '20 at 09:55
Yes, 2 should be there, Checking – mkRabbani Jun 21 '20 at 10:03
Changed a bit @sparsh610 please check – mkRabbani Jun 21 '20 at 10:05

score 0 · Answer 4 · answered Mar 14 '21 at 14:45

0

select s.StudentName 
from Student s
inner join Attendance a on s.StudentId = a.StudId
inner join Lecture l on l.LecId = a.LectureId 
group by s.StudentId, s.StudentName 
having 
    sum(l.Professor= 'JOHN') = (select count(*) from lecture where Professor = 'JOHN')
    and max(l.Professor = 'JOSEPH') = 0
order by s.StudentName

#asked in interview (MakeMyTrip)

answered Mar 14 '21 at 14:45

JoeyTribbbiani

1

Thank you for adding to the list of answers. While some may be able to immediately understand what you've shared, others may not. It will is more helpful to add at least a little context in addition to the code. – untergeek Mar 14 '21 at 19:50

score -1 · Answer 5 · answered Jun 21 '20 at 09:35

I re-phrased the join you did... but it seemed to work.

Does this query return the data set you are looking for?

SELECT s.studentname, l.Subject, l.Professor
FROM Student s JOIN Attendance a
ON s.StudentId = a.StudId
JOIN Lecture l 
ON l.lecId = a.lectureid
WHERE l.professor='JOHN' 
  AND l.professor != 'JOSEPH';

Spartan · Answer 6 · 2021-07-09T14:08:38.927

-1

MSSQL:

WITH cte AS
(
    SELECT StudentName, ROW_NUMBER() 
    OVER(
        PARTITION BY studid
        ORDER BY studid
    ) cou 
    FROM attendance a,lecture l,student s 
    WHERE a.LectureId = l.LecId
    AND a.StudId = s.StudentId
    AND l.Professor = 'john'
)
SELECT StudentName
FROM cte
WHERE cou = 3

Except

SELECT StudentName
FROM attendance a, lecture l, student s 
WHERE a.LectureId = l.LecId
AND a.StudId = s.StudentId
AND l.Professor = 'joseph'

edited Jul 09 '21 at 14:08

answered Jun 24 '21 at 14:10

Spartan

1
3

I'm using partitionby on studid and fetching students who attended all three classes of john(condition on where clause). i use cou=3 to grab students who attended all 3 classes of john. In the next step, I'm using except on a table where students attended joseph's class with the cte table. here i get desired output, table a excludes stdname that are present in table b. – Spartan Jul 09 '21 at 14:21

SQL : join more than one table

6 Answers6