Question 173232 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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′

Tags:

3

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    Your query is very close to what you need, but there is a small error in the syntax. You have an extra comma before the FROM keyword, which is causing the query to fail.

    Here’s a fixed version:

    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'

    This should work and return the result you wanted.

    Login or Signup to reply.
  3. 0

    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 :

    sn Name Age Address Date sn student_id addmission_no Fee
    2 Name 2 Age 2 Address 2 2023-05-21 17 2 6464e25b3ad22 1050
    3 Name 3 Age 3 Address 3 2023-05-21 18 3 6464e25b3ad22 1050
    4 Name 4 Age 4 Address 4 2023-05-21 19 4 6464e25b3ad22 1050

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search