IF expression in SQL
Posted
by chupinette
on Stack Overflow
See other posts from Stack Overflow
or by chupinette
Published on 2010-04-04T16:06:20Z
Indexed on
2010/04/04
16:13 UTC
Read the original article
Hit count: 324
mysql
|conditional
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;
© Stack Overflow or respective owner