Returning rows that has matching record in another table.

Question

I have Table Students with columns Student ID and Student Name. I also have Table Events with columns ID, Student ID and Event.

Now, i want to write a query that will return only rows from Students Table that has its Student ID present in Events Table.

I am running MySql 5+.

Answer 1

You can write WHERE StudentId IN SELECT StudentId FROM Events. (Or at least you can in SQL Server)

Answer 2

SELECT DISTINCT s.* FROM Students s INNER JOIN Events e ON e.StudentId = s.id;

Answer 3

Simple, inner join:

SELECT DISTINCT(Students.Id, Students.StudentName)
FROM Students
JOIN Events on Students.StudentId = Events.StudentId
ORDER BY Students.StudentName

Answer 4

select students.ID, students.name 
from students join events on students.id = events.studentID 
group by students.ID, students.name

Answer 5

SELECT * FROM Students
WHERE StudentID IN (SELECT StudentID 
                    FROM Events 
                    WHERE Events.StudentID = Students.StudentID) 

Answer 6

besides JOIN and IN you can also use EXISTS

SELECT * FROM Students s
WHERE EXISTS  (SELECT * 
                    FROM Events e
                    WHERE e.StudentID = s.StudentID)