Mysql – Fetch 2 different columns same data in 2 different rows

MySQLoraclequery

I have an issue and got stuck very badly.

I have a table product which has 5 columns:

col1
col2
col3
col4
col5

There are a few records where the same value is repeated in different columns and in different rows like this:

 ID | col1  | col2  | col3  | col4  | col5
----+-------+-------+-------+-------+-------
 1  | demo1 | demo2 | demo3 | demo4 | demo5
 2  | demo2 | demo1 | demo5 | demo4 | demo3

Now I want to retrieve all the records where the col1 value and col2 value are same (e.g. demo1) even over multiple rows (e.g. row 1 and row 2).

How can I achieve this?

Best Answer

SELECT a.*, b.*
    FROM product AS a
    JOIN product AS b  ON a.col1 = b.col2

If this presents too many rows, then I need to see SHOW CREATE TABLE to provide filtering to avoid, for example, displaying a row in which col1 = col2 all by itself.