Adding a character to a foreign_key in SELECT statement

Question

Hi,

I've got two tables, e.g. table_1 and table_2. I want to Select the foreign key from table_2 with the id from table_1 but the value of the foreign key in table_2 is the id from table_1 with a prefix.

table_1.id = 1

table_2.fk_id = fk1

How do I add a value to the id so I can select the fk?

Thanks!

Peter

Answer 1

You can do it like this

SELECT * FROM table_1 INNER JOIN table_2 ON CONCAT('fk',CAST(table_1.id AS CHAR))=table_2.fk_id)

However, this is going to be very slow. I would suggest either using an update query on table2 to change all of your fkX ids's to X, or creating a computed column on table1, that creates the id with the fk prefix added.

The reason it's slow is because the join cannot be done using indexes. When you change the key types to be directly comparable, then they can be indexed and will speed up the join.

Answer 2

The real problem here is that's not a real foreign key. It's not enforceable at all. I guess I can assume you're using MyISAM tables?

You should really make them the exact same value and consider adding an additional column to store the prefix