April 25, 2012

MySQL query where item in column a selects item in b, and vice versa

Question by anfuller

I have two columns of data

Refer > Target
1 > 4
1 > 7
2 > 5
3 > 6
4 > 1

I want to run a query in MySQL that selects every refer that is also selected by its target. So, 1 > 4 and 4 > 1.

The furtherest I can get is:

SELECT refer FROM table WHERE refer IN
(SELECT target FROM table)

But that just lists what is in the other column, not what’s matching.

Answer by Starx

I dont think there is a need to over complicate this query

SELECT refer FROM TABLE WHERE (refer = '1' and target = '4') or (refer ='4' and target = '1')

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!