CODEWITHSUNDEEP
mysqlconditional-statements
chupinette
chupinette

Reputation: 456

IF expression in SQL

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;

Upvotes: 1

Views: 404

Answers (2)

confiq
confiq

Reputation: 2918

Well, you can create FUNCTION or PROCEDURE for mysql Syntax should go something like this:

CREATE PROCEDURE insert_item(IN ITEM STRING) 
BEGIN
SELECT item_id FROM quotation WHERE item=ITEM
 IF (item_id > 0)  
  THEN INSERT INTO.... 
  ELSE RETURN FALSE 
 END IF; 
END|

This is a just example, for more info check

http://www.databasejournal.com/features/mysql/article.php/3547931/MySQL-Stored-Procedures-Part-2.htm

Upvotes: 0

Avitus
Avitus

Reputation: 15968

If you use a CASE statement you'll get the desired output.

here is a link on use:

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 2

Related Questions