CODEWITHSUNDEEP
mysql
Owais Alam
Owais Alam

Reputation: 11

How to run two queries on mysql if first one generate no results

This is my first time here and I am a beginner so kindly bear my idiot-ism :)

I want to run two queries in mysql, from two databases in a way that if first query generate s no results then second query should search the same thing in second data base.

We have motor insurance data and there are two databases one for individual customers and second for corporate customers. We need to search vehicle by its chasis no or engine no. from both databases but in a sequence that if from first database no result is found then it should go for second database.

First query:

SELECT * FROM skpoly_vw4 WHERE CHASIS = 878933

Second query: 

SELECT * FROM vw_motorschedule where sw_skpoly_chasis = 878933

WE HAVE ONE DATABASE BUT IN IT WE HAVE TWO DIFFERENT VIEWS WHICH ARE skpoly_vw4 for individual customers and vw_motorschedule for corporate customers

Upvotes: 0

Views: 2366

Answers (3)

Boomer
Boomer

Reputation: 1478

use the SELECT FOUND_ROWS( ) after executing the first query.

that is, before running the second query check if SELECT FOUND_ROWS( )>0

EDIT: Here is an example

SELECT * FROM skpoly_vw4 WHERE CHASIS = 878933

DECLARE @rows INT
SET @rows= FOUND_ROWS();
if (@rows>0)
SELECT * FROM vw_motorschedule where sw_skpoly_chasis = 878933

Upvotes: 0

Fahim Parkar
Fahim Parkar

Reputation: 31647

How about this?

SELECT SQL_CALC_FOUND_ROWS * 
FROM DATABASE1
WHERE database1_CHASIS = 12345**

UNION ALL

SELECT * 
FROM DATABASE2 
WHERE FOUND_ROWS() = 0 AND database2_chasis= 12345**

Demo

Update 1

Per conversation, you are getting mysql error number 1222. This is happening because in table skpoly_vw4 & vw_motorschedule number of fields are not same. For UNION ALL, the fields has to be same.

E.g. In table1 you have id and name and in table2 you have id, fname, lname then if you try to use UNION ALL with select * it won't work as in table1 there are 2 columns however in table2 there are 3 columns.

Hope you got what I meant to say.

Also read select * from two tables with different # of columns.

Upvotes: 2

Achintha Samindika
Achintha Samindika

Reputation: 1954

Try this

$query = mysql_query("SELECT * FROM DATABASE1 WHERE database1_CHASIS = 12345");

if(mysql_num_rows($query)==0){
   $query = mysql_query("SELECT * FROM DATABASE2 where database2_chasis= 12345");
}

$customer = mysql_fetch_assoc($query);

Upvotes: 0

Related Questions