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, Feb 27 '13 at 05: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, Feb 27 '13 at 06:35
@thing guy: time consumption from what perspective? Why do you think 1 monstruous query is faster than 2 trivial? — zerkms, Feb 27 '13 at 07:25

score 5 · Accepted Answer · answered Feb 27 '13 at 05:40

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 '13 at 05:40

Vineet1982

7,730
4
32
67

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? – eirikir May 07 '14 at 20:23
Their is great difficulty between union and found rows but it depends on need and table schema – Vineet1982 May 08 '14 at 03:05
the not exists is a nice solution – Richie Aug 28 '14 at 05:08

chandimak · Answer 2 · 2013-02-27T09:16:50.697

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 '13 at 09:16

answered Feb 27 '13 at 06:03

chandimak

201
3
17

`IFNULL` or `COALESCE`. Refer [this](http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster). – chandimak Feb 27 '13 at 06:18

score 1 · Answer 3 · answered Feb 27 '13 at 06:39

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 '13 at 06:39

Roger

1,693
1
18
34

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

3 Answers3