How to select MySQL rows that have the same content but in different columns?

I have a MySQL table with three columns, X, Y and Z

|    X      |     Y     |      Z    |
-------------------------------------
|   john    |  patrick  |   active  |
|   john    |  miles    | inactive  |
|  patrick  |  john     |   active  |

I'd like to select the row data, given 'john' as the input, corresponding to the pair john-patrick - that is, john and patrick are both in either X or Y (like开发者_StackOverflow中文版 select * rows from TABLE where X = john or Y = john and where ...)

Is it possible? If so, how?

---

If I understand you correctly, the values need to be in X and Y (excluding the tuple ('john', 'miles', 'inactive') in the sample).

Then you will need subquerys for this:

select X,Y,Z from TABLE where
  (X = 'john' or Y = 'john') and 
  X in (select Y from TABLE) and
  Y in (select X from TABLE);

EDIT: The first query was wrong, thanks to ypercube (see comments).

Corrected version:

select X,Y,Z from TABLE where
  (X = 'john' or Y = 'john') and 
  X in (select Y from TABLE as subTable WHERE
         TABLE.X = subTable.Y and TABLE.Y = subTable.X) and
  Y in (select X from TABLE as subTable WHERE
         TABLE.X = subTable.Y and TABLE.Y = subTable.X);

---

select a, b, c from test where b  in (select distinct a from test) or a in (select distinct a from test)
john    patrick active
john    miles   inactive
patrick john    active

---

There's plenty of ways to solve this problem. This is just one:

SELECT * 
FROM users
WHERE 
    (X = 'john' AND Y = 'patrick')
OR
    (Y = 'john' AND X = 'patrick');

---

SELECT
    X, Y, Z 
FROM
    TABLE AS a
  JOIN
    TABLE AS b
      ON  b.X = a.Y
      AND b.Y = a.X
WHERE 
    (a.X = 'john' OR b.X = 'john')

---

Here's your requirements expressed as SQL:

select a.X, a.Y, a.Z
from mytable a
join mytable b on a.X = b.Y and a.Y = b.X
where a.X = 'john';

This is very similar to ypercube's answer, but differs in one small but very important way: there is no need for or b.Y = 'john' in the where clause, because this is logically covered by the on clause of the join (the join says a.X = b.Y, so if a.X = 'john' we know already that b.Y = 'john').

This difference might seem small, but is large in terms of performance: Databases will not use indexes when OR is used to match indexed columns, ie:

where col = 'x' or col = 'y' -- no: will not use index on col

where col = 'x'              -- yes: will use index on col

My query will use an index on column X if one exists). If one doesn't exist, create one.

Incidentally, to get the database to use an index in an OR on a given column, use IN instead. These next two lines are logically identical, but perform very differently

where col = 'x' or col = 'y' -- no: will not use index on col

where col in ('x', 'y')      -- yes: will use index on col

---

You don't need a join for this.

select X,Y,Z from users where X like '%john%' or Y like '%john%';