CODEWITHSUNDEEP
mysqlsql
HiddenHopes
HiddenHopes

Reputation: 63

SQL Query From two Table satisfying a condition

Table 1 name: Student
Name      Department
Alex         CSE
Bob          EEE
Briyan       ME
John         CSE
James        ETE
Mike         CE

Table 2 name: Program
Department    Semester
   CSE         Summer
   CSE         Winter
   EEE         Summer
   ETE         Summer
   ME          Winter
   CE          Summer
   CE          Winter

What is the SQL query to print the names and the semester where each student's department have only 1 type of semester? (the server is mysql)

SQL Query Result should be like this:
Name     Semester
Bob       Summer
Briyan    Winter
James     Summer

Here, CSE and CE have both summer and winter semester. So Alex, John and Mike are not included.

I have tried this far:

SELECT Student.Name,
     Program.Semester
FROM Student
JOIN Program
    ON Program.Department = Student.Department
WHERE ....

Upvotes: 0

Views: 136

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a In clause on a subquery whetre coubt the department with only a entry

  select a.Name, b.Departmnet 
  from Student a
  inner join program b on a.Department = b.Department and b.Department In (
  select Department from Program
  group by Department having count(*)=1 
  )

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to join students with single-semester departments. So aggregate your program data by semester to get the single-semester departments. As there is exactly one semester for these departments, you can get the semester with any_value(semester).

select s.name, d.the_semester
from student s
join
(
  select any_value(semester) as the_semester
  from program
  group by department
  having count(*) = 1
) d on d.department = s.department
order by s.name;

Another way would be to get all student semester combinations and the group by student to keep only single-semester students.

select s.name, any_value(p.semester) as the_semester
from student s
join program p on p.department = s.department
group by s.name
having count(*) = 1
order by s.name;

Choose whichever query you like better.

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

CREATE TABLE #Student(Name NVARCHAR(50),Department NVARCHAR(50))
CREATE TABLE  #Program (Department NVARCHAR(50),Semester NVARCHAR(50))

INSERT INTO #Student
SELECT 'Alex','CSE' UNION ALL 
SELECT 'Bob','EEE'   UNION ALL 
SELECT 'Briyan','ME' UNION ALL 
SELECT 'John','CSE' UNION ALL 
SELECT 'James','ETE' UNION ALL 
SELECT 'Mike','CE'


INSERT INTO #Program
SELECT 'CSE','Summer' UNION ALL 
SELECT 'CSE','Winter' UNION ALL 
SELECT 'EEE','Summer'UNION ALL 
SELECT 'ETE','Summer'UNION ALL 
SELECT 'ME','Winter'UNION ALL 
SELECT 'CE','Summer'UNION ALL 
SELECT 'CE','Winter'

;WITH CTE AS (
  SELECT Name ,MAX(b.Semester)semester
  FROM #Student a INNER JOIN  #Program b ON a.Department=b.Department
  group by name
  having count(distinct semester) = 1

)
SELECT * FROM CTE 

GO

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use aggregation and filter in having clause for those names which have only one semester:

select s.name,
    max(p.semester) as semester
from student s
join program p
    on s.department = p.department
group by s.name
having count(distinct p.semester) = 1;

Upvotes: 0

Related Questions