CODEWITHSUNDEEP
mysql
Shawon
Shawon

Reputation: 1030

Mysql- Select Query from two table

I have two table item_info and item_log tables are given below:

Item_info

Item_log

Now i need to search by 'like' item_name and the result will show last row of any item_id like:

Result

Upvotes: 1

Views: 71

Answers (4)

Ahosan Karim Asik
Ahosan Karim Asik

Reputation: 3299

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

Upvotes: 0

Mahesh Patil
Mahesh Patil

Reputation: 1551

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 ?

Upvotes: 0

Shawon
Shawon

Reputation: 1030

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

Upvotes: 1

user07192010
user07192010

Reputation: 44

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

Upvotes: 1

Related Questions