MySQL Join using "contains"?

Question

I have these two tables:

Table: ORDERS
ID - NAME    - DATA
---------------------------------------------
1  - Order 1 - node_checkout_nid";i:141;s:10:
2  - Order 2 - node_checkout_nid";i:142;s:10:

Table: NODES
NID - Description
--------------------
141 - bla bla bla
142 - bla bla bla 2

I need a SQL join query that can join the two tables on NID, keeping in mind the NID is in the "DATA" column.

I need to end with this:

Table: RESULT
ID - NAME    - DATA                             NID - Description
-----------------------------------------------------------------------------------
1  - Order 1 - node_checkout_nid";i:141;s:10: - 141 - bla bla bla
2  - Order 2 - node_checkout_nid";i:142;s:10: - 142 - bla bla bla 2

I wanted to use a "like" join, but I think (if it's possible) a "contains" join would be better? Any help would be greatly appreciated!

Answer 1

You can use:

SELECT ID, NAME, DATA, NID, Description
  FROM ORDERS INNER JOIN NODES ON DATA LIKE CONCAT('%;i:', NID, ';%')

Anyway it's a heavy query. It's not a good idea to have the NID inside the data field as a plain text, better in a different column.

Answer 2

You data is denormalised. You're storing some serialized object in DATA column and are trying to perform relational operations basing on that. While it is possible to do so, mind that it will be slow and potentially unreliable. Just store your NID in a separate column.