select student name with profession that apper more the once-CodePudding

sorry for the question, I'm usually using PostgreSQL but MySQL I can't find my legs In it, I have this DB :

create table Students (
  Student_id int,
  Student_name varchar(100)
  );
  
  create table Grades
  (
    student_id numeric,
    Profession varchar(10),
    Grade numeric
  );
  
  insert into Students(Student_id,Student_name) values (1,"avi"),(2,"david"), (3,"mosh"), (4,"adir");
  
  
  insert into Grades values (2,'math',95), (3,"sport",25), (4,"english",30);
  
  insert into Grades(student_id,Profession) values (1,'math');

I want to get the student name the other profession paper more than once in this example the output will be:

avi 
david

because there both learning math

this is my attempt so far:

select 
      student_name,
      profession
from Students
inner join Grades
on Students.Student_id = Grades.Student_id
group by profession 
count(profession) > 1;

this is a link to SQL fiddle

CodePudding user response：

Subquery returns profession wise student count and join with student and grades for retrieving final result where no_of_student per profession appears more than one.

-- Mysql
SELECT s.student_name
FROM Students s
inner join Grades g
        on s.Student_id = g.Student_id
inner join (SELECT Profession
                 , COUNT(1) no_of_std
            FROM Grades
            GROUP BY Profession) t
        on g.Profession = t.Profession
       AND t.no_of_std > 1
order by s.student_id

Please check this url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a59293c694549e39bcf1678050f6b6f1

N.B.: This query is also applicable for all RDBMS (PostgreSQL, MSSQL, MySQL, ORACLE) because all basics syntax are used here.

Latest version of mysql(v5.8) where analytical function supports and this will also work postgresql, MSSQL etc

SELECT t.student_name
FROM (SELECT s.Student_id, s.student_name
           , COUNT(s.Student_id) OVER (PARTITION BY Profession) no_of_std
      FROM Students s
      inner join Grades g
              on s.Student_id = g.Student_id) t
where t.no_of_std > 1

Please check this url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f9bced508d1bfc77aacd8b5826aa6a38

For PostgreSQL, please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=1555612e72404eddc820c254c708304a