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 two tables in an NID, bearing in mind that 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 “similar” connection, but I think (if possible) the connection “contains” 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, ';%') 

In any case, this is a heavy request. It’s nice to have a NID inside the data field as plain text, preferably in a different column.

Answer 2

Data is denormalized. You save some serialized object in a DATA column and try to perform relational operations based on this. While this can be done, remember that it will be slow and potentially unreliable. Just save your NID in a separate column.