CODEWITHSUNDEEP
mysqlsql
mk_89
mk_89

Reputation: 2742

Matching rows using LIKE column value

I am trying to match id's between different tables using LIKE instead of using JOINS, this is necessary because of the following scenario

I have 2 tables, the first is called order, the second order_items.

For each order there can be multiple order items so for example typical table values may look something like the following...

Table orders

id      sell_price       buy_price
0001    50               20


Table order_items 

id      sell_price       buy_price
0001-1  30               15
0001-2  20               5

I have tried creating a query that will be able to return all items for order 0001 using LIKE returning order items 0001-1 and 0001-2.

SELECT 
o.id AS order_id, 
oi.id AS order_item_id,
oi.sell_price AS order_item_sell_price, 
oi.buy_price AS order_item_buy_price  
FROM orders o, order_items oi 
WHERE oi.id LIKE o.id + '%' 
ORDER BY o.purchase_date DESC

No results are returned!

The problem seems to be with the LIKE statement, I thought this was the correct way to declare it but I guess not, any help would be appreciated.

Upvotes: 1

Views: 71

Answers (2)

fajarkoe
fajarkoe

Reputation: 1563

It is better to move out the ID of the ORDER from the ID of the ORDER_ITEMS into another column ORDER_ID in ORDER_ITEMS. The reason is that you can index ORDER_ID column, which makes your SQL query runs faster.

Upvotes: 1

DarkAjax
DarkAjax

Reputation: 16223

Try using CONCAT:

SELECT 
o.id AS order_id, 
oi.id AS order_item_id,
oi.sell_price AS order_item_sell_price, 
oi.buy_price AS order_item_buy_price  
FROM orders o, order_items oi 
WHERE oi.id LIKE CONCAT(o.id, '%')
ORDER BY o.purchase_date DESC

And you can still use a JOIN if you want:

SELECT 
o.id AS order_id, 
oi.id AS order_item_id,
oi.sell_price AS order_item_sell_price, 
oi.buy_price AS order_item_buy_price  
FROM orders o
JOIN order_items oi on oi.id LIKE CONCAT(o.id, '%')
ORDER BY o.purchase_date DESC

A simplified sqlfiddle: http://sqlfiddle.com/#!2/e6b12/6

Upvotes: 2

Related Questions