Join table and get unique rows based on where condition

I have two tables, both tables have a column with the same value.

Table 1

|sn|Name  |Age  |Address  |Date      |
|1 |Name 1|Age 1|Address 1|18-05-2023|
|2 |Name 2|Age 2|Address 2|21-05-2023|
|3 |Name 3|Age 3|Address 3|21-05-2023|
|4 |Name 4|Age 4|Address 4|21-05-2023|

Table 2

|sn|student_id|addmission_no|transaction_no|Fee |transaction_date|
|10|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|11|2         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|12|3         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|13|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|14|2         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|15|3         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|16|1         |6464e25b3ad22|312009677062  |1050|17/05/2023      |
|17|2         |6464e25b3ad22|312009677062  |1050|20/05/2023      |
|18|3         |6464e25b3ad22|312009677062  |1050|20/05/2023      |
|19|4         |6464e25b3ad22|312009677062  |1050|20/05/2023      |

now I want data from both tables but only rows where Table 1 sn matches with Table 2 student_id and where the date in Table 1 is 21-05-2023, it should return 3 rows.

I want the date like this.

<------Table 1 Data-----------------><----------Table 2 Date---------->
|sn|Name  |Age  |Address  |Date      |sn|student_id|addmission_no|Fee |
|2 |Name 2|Age 2|Address 2|21-05-2023|17|2         |6464e25b3ad22|1050|
|3 |Name 3|Age 3|Address 3|21-05-2023|18|3         |6464e25b3ad22|1050|
|4 |Name 4|Age 4|Address 4|21-05-2023|19|4         |6464e25b3ad22|1050|

I have tried this

> SELECT table1.sn , table1.name, table1.age, table1.address,
> table1.date, table2.sn, table2.student_id, table2.addmission_no,
> table2.Fee, FROM table1 INNER JOIN table2 ON
> table1.sn=table2.student_id where table1.date='21-05-2023'

it gives me all the rows where student_id matches with table1.sn

I see you're only interested in the records with the most recent transaction date from table2.

You need to join again with a dataset of student ids and their associated max transaction date, using group by and max()

SELECT t1.*, t2.sn, t2.student_id, t2.addmission_no, t2.Fee
FROM   table1 t1
INNER JOIN   table2 t2 on t1.sn = t2.student_id
INNER JOIN (
  SELECT student_id, max(transaction_date) as max_transaction_date
  FROM table2
  GROUP BY student_id	
) as s ON s.student_id = t2.student_id AND s.max_transaction_date = t2.transaction_date
WHERE  t1.`date` = '2023-05-21'

Result :

Demo here

You can join on the relationship between sn and student_id and add a where clause:

SELECT t1.*, t2.*
FROM   t1
JOIN   t1.sn = t2.studdent_id
WHERE  t1.`date` = DATE'21-05-2023'