CODEWITHSUNDEEP
mysqlsqlinner-joinself-join
Faisal
Faisal

Reputation: 4765

How to use inner join in the same table

I have a table name as tnx_line_transfer. Schema like below

enter image description here

Now i want to generate output like below

Output

enter image description here

I am trying below query but its not showing proper output. what is the issue on this query 

SELECT
    main_tbl.operator_id,
    main_tbl.production_line
FROM
    tnx_line_transfer main_tbl
INNER JOIN (
    SELECT
        operator_id,
        max(date) AS max_date
    FROM
        tnx_line_transfer
    GROUP BY
        operator_id
) temp ON main_tbl.operator_id = temp.operator_id

If any other alternative easy solution please let me know.Thanks in advance.

Upvotes: 2

Views: 10694

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

You missed to join date column with max_date. 

SELECT main_tbl.operator_id,
       main_tbl.production_line
FROM   tnx_line_transfer main_tbl
       INNER JOIN (SELECT operator_id,
                          Max(`date`) AS max_date
                   FROM   tnx_line_transfer
                   GROUP  BY operator_id) temp
               ON main_tbl.operator_id = temp.operator_id
                  AND main_tbl.max_date = temp.`date` --here

Another way would be using Sub-Query

SELECT main_tbl.operator_id,
       main_tbl.production_line
FROM   tnx_line_transfer main_tbl
WHERE  `date` = (SELECT Max(`date`)
               FROM   tnx_line_transfer temp
               WHERE  main_tbl.operator_id = temp.operator_id)

Row_Number concept comes in handy for above scenario but unfortunately Mysql doesnot support Window function's

Upvotes: 4

Related Questions