Mysql column's row starts with value of another row

Question

Hello all, I have a table like below:

 MyTable
 --------
  A
  B
  C

A,B,C are columns and MyTable is the table name and i want run a mysql query like:

SELECT MT1.A, MT2.A, MT2.B FROM MyTable MT1, MyTable MT2
       WHERE MT1.B<>MT2.B and MT2.B like "MT1.B%" and MT2.status=0;

As you see from the query above i have a table and i want find the columns that starts with another rows value and matches the conditions. But above query obviously fails because mysql takes "MT1.B%" as a string, how can i achieve this with Mysql?

Answer 1

You should be able to do the following:

SELECT 
    MT1.A, MT2.A, MT2.B
FROM 
    MyTable MT1, MyTable MT2
WHERE
    MT1.B <> MT2.B AND 
    MT2.B LIKE CONCAT(MT1.B, '%') AND 
    MT2.status = 0

Answer 2

SELECT  MT1.A, MT2.A, MT2.B
FROM    MyTable MT1, MyTable MT2
WHERE   MT1.B<>MT2.B
        AND MT2.B LIKE CONCAT(MT1.B, '%')
        AND MT2.status=0