I have two table item_info and item_log tables are given below:
Now i need to search by 'like' item_name and the result will show last row of any item_id like:
4 Answers
can you try something like this:
SELECT table1.*, MAX( table1.id ) AS last_id,
table2.table2.id
FROM (table1)
JOIN table2 ON table1.table2.id = table2.table2.id
WHERE item_name LIKE '%nameOfItem%'
GROUP BY table1.table2.id
ORDER BY table1.id desc
it's kinda same with my function, i have to retrieve recent message for every thread. so i have to join the tables and group it by message id, and in the descending order in which the message has sent.
basically, the logic is quite the same. let me know if it doesn't work
2 Comments
Shawon
last_id correct but other information not match@user07192010
user07192010
You can just modify it depending on your desired outputs
Problem Solved
SELECT item_log.*,item_info.name
FROM (item_log)
JOIN item_infos ON item_log.item_id = item_infos.item_id
WHERE id IN (SELECT MAX( item_log.id )
FROM (item_log)
JOIN item_infos ON item_log.item_id = item_infos.item_id
WHERE item_name LIKE '%G%'
GROUP BY item_log.item_id
ORDER BY item_log.id desc)
AND item_name LIKE '%G%'
GROUP BY item_log.item_id
ORDER BY item_log.id desc
Comments
SELECT
*
FROM
(SELECT
item_info.`item_name`,
item_log.`item_id`,
item_log.`id`,
item_log.`item_barcode`,
item_log.`buy_price`
FROM
item_log,
item_info
WHERE item_info.`item_id` = item_log.`item_id`
ORDER BY id DESC) AS i
GROUP BY item_id ;
I think this is what you are expecting right ?
Comments
This may work for you:
SELECT l.id,i.item_id,l.item_barcode,i.item_name,l.buy_price
FROM item_info i
LEFT JOIN item_log l ON i.item_id=l.item_id
WHERE i.item_name LIKE '%G%'
ORDER BY l.id desc
item_log.item_idfield is not looking unique, then how to decide which rows will come fromitem_logtable.