I have a table like this. The last digit in the ‘Subject’ column indicates the Semester.
From the above table, I want a table like this
The logic is that: I want to find the list of candidates who will appear in ‘CEMG3’ with a condition. The condition is that the candidate must have to appear in at least two subjects for the same semester. So Roll no 3 will not be included in the table as though the candidate is appearing in ‘CEMG3’, but he/she is not appearing in any other subjects in the 3rd semester.
I have tried the following query. But it can not give me the desired result as
Group by removes few rows containing ‘CEMG3’. Again without
Group By, counting is becoming difficult for me.
$ss1a=mysqli_query($con, "select RollNo, Subject from (select RollNo, count(RollNo), Subject FROM table where mid(Subject, 5,1)='3' group by RollNo having count(RollNo)>1) where Subject='CEMG3' ");
Please guide me in this regard.
Join with a subquery that finds the candidates who are in at least two subjects in the 3rd semester.
SELECT t1.Roll, t1.Subject FROM table AS t1 JOIN ( SELECT Roll FROM table WHERE RIGHT(Subject, 1) = '3' GROUP BY Roll HAVING COUNT(*) > 1 ) AS t2 ON t1.Roll = t2.Roll WHERE t1.Subject = 'CEMG3'