If a select query return empty result, run another select query in mysql

Question

I have 2 select statements.

1- select sm.id, IFNULL(sm.unit_price,0) as unit_price       
   from crm_stock_manager sm
   left join crm_purchase_order_items poi on poi.id = sm.purchase_order_item_id and  poi.is_del = 0
   left join crm_products p on poi.product_id = p.id and p.is_del = 0
   where sm.is_del = 0 and IFNULL(sm.unit_price, '') != '' and p.id = 253 order by sm.created_on;

2- select 0 as id ,price as unit_price from crm_products where id = 253  and is_del=0;

If 1st select statement return an empty result, then i want to run 2nd select statement.

Please help me.

Does this mean that you need to select crm_products.price if srm_stock_manager.unit_price is NULL? — chandimak
– chandimak, Commented Feb 27, 2013 at 5:44
Noop. if crm_stock_manager table contains any data i need to take the records from that table. other wise it will take from crm_products table. — CodeCook
– CodeCook, Commented Feb 27, 2013 at 6:35

Vineet1982 · Accepted Answer · 2013-02-27 05:40:43Z

5

This appears to work from a quick test I just did and avoids the need to check for the existence of x=1 twice.

SELECT SQL_CALC_FOUND_ROWS *
FROM mytable
WHERE x = 1

UNION ALL

SELECT *
FROM mytable
WHERE 
FOUND_ROWS() = 0 AND x = 2;

Another way is Subqueries with EXISTS or NOT EXISTS

SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);

answered Feb 27, 2013 at 5:40

Vineet1982

7,9285 gold badges34 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eirikir Over a year ago

Has the behavior of FOUND_ROWS() across SELECTs in the same UNION query changed? I'm seeing this method working for MySQL 5.1.61 but not for 5.6.15. In the latter case, FOUND_ROWS() in the second SELECT seems to return for the previous query instead of for the previous SELECT. Am I missing something?

Vineet1982 Over a year ago

Their is great difficulty between union and found rows but it depends on need and table schema

Richie Over a year ago

the not exists is a nice solution

Roger · Accepted Answer · 2013-02-27 06:39:53Z

1

You can do it like this

$query1 = mysql_query("YOUR QUERY");
$row_count = mysql_num_rows($query1);
if($row_count==0) {
 $query1 = mysql_query("Your Second Query");
}

Remember to not use mysql_* functions as they are depreciated.

Hope This Helps

answered Feb 27, 2013 at 6:39

Roger

1,7011 gold badge18 silver badges34 bronze badges

Comments

chandimak · Accepted Answer · 2013-02-27 09:16:50Z

1

If you need to select crm_products.price if srm_stock_manager.unit_price is NULL, you can use COALESCE and you need not 2 queries. You can change your first part of SELECT statement as follows and other part as necessary to get the result required.

SELECT sm.id, COALESCE(sm.unit_price, p.price) unit_price FROM...

This returns p.price value if sm.unit_price is NULL.

Further, refer mysql doc.

edited Feb 27, 2013 at 9:16

answered Feb 27, 2013 at 6:03

chandimak

2013 silver badges17 bronze badges

1 Comment

chandimak Over a year ago

IFNULL or COALESCE. Refer this.

Collectives™ on Stack Overflow

If a select query return empty result, run another select query in mysql

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related