CODEWITHSUNDEEP
mysqlsqldatabase
CSA75
CSA75

Reputation: 27

Join two tables based on a foreign key which a substring of column?

I have the two following tables

Order

ID|      DETAILS       | AMOUNT
-------------------------------
0 |#Battery#Client1234 | 90USD

Client

ID   |  NAME
--------------
1234 | JohnDoe

How can I join these two tables since the foreign key in the Order table is aggregated with some other information ?

Upvotes: 0

Views: 160

Answers (4)

cdaiga
cdaiga

Reputation: 4939

You can extract the client id from the order details using MySQL SUBSTRING_INDEX() function and use it to do a join with the client table. Here is the query:

SELECT *
FROM `order` o
LEFT JOIN client c
ON c.id=SUBSTRING_INDEX(o.details,'#Client',-1);

SQL FIDDLE DEMO

Upvotes: 0

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/a8c9f6/1

SELECT o.*, c.*
FROM `order` o
LEFT JOIN client c
ON o.details LIKE CONCAT('%#Client',c.id)

Upvotes: 1

jimmu
jimmu

Reputation: 1056

Try:

SELECT * 
FROM Orders oo
JOIN Client cc
ON oo.details LIKE CONCAT('%', cc.id, '%');

Upvotes: 0

Anton Yaskiv
Anton Yaskiv

Reputation: 42

Break your column DETAILS into two OrderDerails and CLientID and join ClientID with second table.

Upvotes: 3

Related Questions