Cannot select rows separately without grouping

Question

I have an SQL query that should capture the program, class, and year from my students table, given the two columns of X and Y numbers. For example, if x = 3 and Y = 4, this means there is a connection between students 3 and 4, and I will choose a program, class, year from student 4 for our argument.

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

(because there may be two connections), SELECT will select the program, class and year only once, not twice. I want him to select this repetition individually, so my cycle will repeat this later twice.

I hope I get it!

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

Answer 1

The JOIN that I mentioned in the 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 do not forget to sanitize your entries (that is, make sure that there is no ' in $ id ' ); or better, look at parameterized queries