Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn't end in that character I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also. 

+------------+
|    key     |
+------------+
| value_100  |
| value_100P |
| value_101  |
| value_101  |
| value_102  |
| value_102P |
| value_102P |
| value_102P |
| value_103P |
| value_103P |
| value_104P |
+------------+


The query would output, 

+------------+
|    key     |
+------------+
| value_100P |
| value_102P |
+------------+
How about this : 

select t1.key 
from table1 t1 
inner join table1 t2 
      on   t1.key = t2.key || 'P' 
where t1.key like '%P' 
order by t1.key ; 


Querying t1 gives you a list of all of the rows with keys that end in 'P'. 

The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1  (or, alternatively, the original key without the trailing 'P').