Why not equal displaying actual result in select queries by joining two select tables-CodePudding

I am using mysql. I am trying to join two queries by != condition. For this example it should return empty result set. But it seems the condition is not applied. I am not understanding why. Can anybody please help? My attempts are below:

    SELECT today_student.* FROM (
    SELECT scd.student_id, sc.transaction_date
    FROM student_collection_details scd
    INNER JOIN student_collection sc
     ON (scd.student_collection_id = sc.id)
    WHERE 1=1
    AND sc.transaction_date BETWEEN DATE('2022-06-01 00:00:00') AND DATE('2022-06-27 00:00:00')
    AND scd.admission_year_id = 2
    AND scd.month_id = 21
    AND scd.collection_head_id = 9  
    GROUP BY scd.student_id
) prev_student,

(
    SELECT scd.student_id, sc.transaction_date
    FROM student_collection_details scd
    INNER JOIN student_collection sc
     ON (scd.student_collection_id = sc.id)
    WHERE 1=1
    AND sc.transaction_date = DATE('2022-06-28 00:00:00')
    AND scd.admission_year_id = 2
    AND scd.month_id = 21
    AND scd.collection_head_id = 9  
    GROUP BY scd.student_id
) today_student

WHERE 1=1
AND  prev_student.student_id != today_student.student_id

prev_student returns:

    1196; 2022-06-20 00:00:00
    1861; 2022-06-18 00:00:00

today_student returns:

1196; 2022-06-28 00:00:00
1861; 2022-06-28 00:00:00

CodePudding user response：

Use a HAVING clause with the condition that the min transaction_date is '2022-06-28':

SELECT scd.student_id, 
       MIN(sc.transaction_date) transaction_date
FROM student_collection_details scd INNER JOIN student_collection sc
ON scd.student_collection_id = sc.id
WHERE sc.transaction_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-28 00:00:00'
  AND scd.admission_year_id = 2
  AND scd.month_id = 21
  AND scd.collection_head_id = 9
GROUP BY scd.student_id
HAVING MIN(sc.transaction_date) = '2022-06-28 00:00:00';