Query in different columns at the same time in Mysql

Question

I have two tables, the first doesn’t matter for now, the second follows example below and we will name from table2:

id	column1	column2
1	valor1	value2
2	value2	valor1
3	Valor3	value4
4	value5	value6

And I have the following appointment:

SELECT * FROM tabela1
WHERE coluna1 IN (SELECT coluna1 FROM tabela2)

The point is, I’d like him to do that select between parentheses only when the same values exist, but in different columns that in the case would be only rows 1 and 2 of my example, the same values exist in the bottom row, but in different columns. Would it be possible? In the query I would like it to return the result below:

id	column1	column2
1	valor1	value2
2	value2	valor1

Answer 1

He’s a little confused but from what I understand I could say that

return records where the value of "column1" also exists in "colonnade 2" and vice versa

For that you need to use the EXISTS, who will check if there is a record with a condition in a subquery.

Dai, you need to do the subquery validate column1 and column2, for example:

select coluna1 from tabela2
where  exists (select coluna2 
                 from tabela2 t2
                where t2.coluna2 = tabela2.coluna1)
    or exists (select coluna1 
                 from tabela2 t2
                where t2.coluna1 = tabela2.coluna2)

That would be the subquery inside the IN.
You can see it working here (in the example I used only numbers to simplify) : http://sqlfiddle.com/