Can't select rows individually without grouping

Question

I have an SQL query that is supposed to grab programme, class and year from my Students Table, given two columns X and Y of numbers. If x=3 and Y=4, for instance, it means there's a connection between student 3 and 4 and I will select programme, class, year from student 4, for the sake of our argument.

The problem is, if there are two rows in which X and Y are equal, for example : Row 1: X=3, Y=4 Row 2: X=3, Y=4

(because maybe there are two connections), the SELECT will only select programme, class, and year just ONCE, not twice. I want it to select that repetion individually, so my loop later will echo that twice.

I hope I made myself clear!

$SQL = "
  SELECT Programme, Class, Year 
  FROM Students
  WHERE  Student_ID in (
    SELECT X
    FROM SearchStudent
    WHERE Y= '$id'
  )";

Answer 1

The JOIN I mentioned in comment will probably look something like this:

SELECT s.Programme, s.Class, s.Year 
FROM SearchStudent AS ss
INNER JOIN Students AS s ON ss.X = s.Student_ID
WHERE ss.Y= '$id'
;

And don't forget to sanitize your inputs (i.e. make sure $id has no ' in it); or better, look into parameterized queries