为什么不加入两个选择表，在选择查询中显示实际结果

Question

我正在使用mysql。我正在尝试通过！=条件加入两个查询。对于此示例，它应该返回空结果集。但是看来这种情况没有应用。为什么这样？

我的尝试如下：

    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返回：

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

today_student返回：

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

Answer 1

使用子句的条件 条款，即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';