Mysql – Join queries are not working as expected when trying to compare a count result with a value

join;MySQLquery

I'm learning SQL from a book and I'm trying to do some exercices on join queries. The only problem that I'm facing is that all of my join queries are not working while they seem well.

These are the tables:

    students(student_id,student_names,student_age)
    courses_students(course_id,student_id)
    courses(course_id,course_schedule,course_room,teacher_id)
    teachers(teacher_id,teacher_names)

The query is "which courses have more than 5 students enrolled?"

Here is what I've done:

select 
  course_name,
  count(select count(*) from courses) as count 
from students,courses,courses_students 
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id 
and count > 5

And the other one is "what are the names of students enrolled in at least 2 courses scheduled for the same hours?"

My query:

select student_name,schedule 
from students,courses,courses_students 
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id 
and count > 2

Best Answer

For the first question "which courses have more than 5 students enrolled?"

try the following query

select a.course_id,b.course_name,count(a.student_id) as student_count from courses_students a join courses b on a.course_id=b.course_id group by a.course_id having student_count>5;

For the second question "what are the names of students enrolled in at least 2 courses scheduled for the same hours"

try this

select a.student_id,a.student_name,b.course_schedule,count(b.course_id) as course_count from students a join courses b join courses_students c on b.course_id=c.course_id and a.student_id=b.student_id group by a.student_id,b.course_schedule having course_count>1;

Related Solutions

MySQL Join – How to Exclude Certain Records in a Join

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.

This is TSQL so it may be wrong for mysql

SELECT S.ID
     , S.LASTNAME
     , S.FIRSTNAME
  FROM STUDENTS S
  LEFT JOIN TEACHERS T 
        ON T.STUDENT_ID = S.ID
       AND T.TEACHER_ID = $teacherID 
 WHERE T.TEACHER_ID is null

SQL Server – RIGHT JOIN Not Working as Expected

If I understand your requirements correctly, you want each row in DCString, even when there is no matching row in StringData. The two queries you have now are incorrect:

The first one performs DCString RIGHT JOIN StringData - this is backward to what you seem to want (and is one of the reasons most sane people avoid RIGHT JOIN whenever possible).
The second query performs StringData LEFT JOIN DCString - this is also backward to what you seem to want. I suggested changing the RIGHT JOIN to a LEFT JOIN, without changing the table order, but you swapped the tables too, for no net change. Why that one is currently not returning could be due to a lot of things, no way for us to speculate. Try and generate an estimated plan and see if you are falling prey to any of these reasons.

Again, based on what I think you want (you'll get better answers if you provide some sample data and desired results):

-- don't use regional, ambiguous formats like dd.mm.yyyy:
-- also you can declare multiple variables in one statement

DECLARE @begin datetime    = '20160222',
        @end   datetime    = '20160223',
        @dcStringID bigint = 6658;

-- don't litter your code with square brackets except where necessary:

SELECT d.ID, s.[TimeStamp] -- bad choice for a column name

-- always use schema prefix!
FROM dbo.DCString AS d
LEFT OUTER JOIN dbo.StringData AS s
  ON d.ID = s.DCStringID
  AND s.[TimeStamp] >= @begin
  AND s.[TimeStamp] <  @end
WHERE d.ID = @dcStringID;

Further reading:

Related Question