INSERT INTO item_quotation
(item_id, quotation_id,name_searched,item_name,other_name,selling_price,discounted_price)
SELECT DISTINCT I.item_id," . $quotation_id . ",T.item_name, I.name,I.other_name, INV.selling_price, I.discounted_price
FROM temp_quotations T, item I, inventory INV<br/>
WHERE ( I.name LIKE CONCAT( '%', T.item_name, '%' )
OR I.other_name LIKE CONCAT( '%', T.item_name, '%' ))
AND INV.item_id = I.item_id;
I have a table called temp_quotations(temp_item_id,item_name)having values
(1,grinder), (2,drill), (3,kit)
I have another table called item(item_id,name,other_name,discounted_price) having values
(1,grinder A,100),(2,kit A,200)
When I execute this sql, it insert values in table item_quotation(item_id, quotation_id,name_searched,item_name,other_name,selling_price,discounted_price) for example in this case it will insert
(1,1,grinder,grinder A,150,100)
(2,1,kit,kit A, 250,200)
Since the item drill is not found in table item, it wont display it in table item_quotations. I want to write an if statement within that sql so that when the item_name from temp_quotation does not match the name from item, it displays not found in the place of item_name.
This is what i tried but it does not display "not found":
INSERT INTO item_quotation
(item_id, quotation_id,name_searched,item_name,other_name,selling_price,discounted_price)
SELECT DISTINCT I.item_id," . $quotation_id . ",T.item_name,
IF(I.name LIKE CONCAT( '%', T.item_name, '%' ),I.name,'not found'),I.other_name, INV.selling_price, I.discounted_price
FROM temp_quotations T, item I, inventory INV
WHERE ( I.name LIKE CONCAT( '%', T.item_name, '%' )
OR I.other_name LIKE CONCAT( '%', T.item_name, '%' ))
AND INV.item_id = I.item_id;
