Advertisement

Mysql select the same values in different rows and different column

         new_id                                          old_id
         000                                              333
         111                                              666
         222                                              555
         333                                              777

I want to select the rows with the same values in old_id and new_id so my select should return row 1 and 4 from the example

SELECT *
 FROM `table` AS `t`
      INNER JOIN ( SELECT * FROM `table`  ) as `old`
      ON `old`.`old_id` = `t`.`new_id`

I have this query but it doesn’t work it just returns all the records

Advertisement

Answer

Try this below logic-

SELECT DISTINCT C.* FROM 
(
    SELECT A.new_id
    FROM your_table A
    INNER JOIN your_table B ON A.new_id = B.Old_id
)A
INNER JOIN your_table C 
ON A.new_id = C.new_id
OR A.new_id = C.old_id

Advertisement