Page 1 of 1

merge two mysql queries

Posted: Sun Oct 24, 2010 2:13 pm
by JKM

Code: Select all

$query1	= mysql_query("SELECT * FROM `db`.`table` WHERE active=0");
$query2 = mysql_query("SELECT * FROM `db`.`table` WHERE active=1");
$query	= // merge $query1 and $query2;
$rows	= mysql_num_rows($query);
while($fetch = mysql_fetch_object($query)) {
	//
}
Is this possible?

Re: merge two mysql queries

Posted: Sun Oct 24, 2010 2:26 pm
by John Cartwright
If you active column is only 0/1, then you could simply omit the WHERE clause alltogether. Otherwise, just combine the WHERE clause conditions.

Code: Select all

$query1 = mysql_query("SELECT * FROM `db`.`table` WHERE active=0 OR active =1");
If you truly wanted to "merge" the two queries, alas unions.

Code: Select all

$query1 = mysql_query("
   (
      SELECT * FROM `db`.`table` WHERE active=0
   ) UNION (
      SELECT * FROM `db`.`table` WHERE active=1
   )
");

Re: merge two mysql queries

Posted: Sun Oct 24, 2010 4:29 pm
by Eran
Side note - those queries are exactly the same (OR is in fact a UNION operation)

Re: merge two mysql queries

Posted: Sun Oct 24, 2010 4:46 pm
by John Cartwright
Eran wrote:Side note - those queries are exactly the same (OR is in fact a UNION operation)
Are they executed the exact same?

Is the query basically rewritten to express the query using WHERE logic? Or are they executed seperately -- then -- combined?

Re: merge two mysql queries

Posted: Sun Oct 24, 2010 5:10 pm
by Eran
The query is executed as a UNION as far as I understand it. The query plan should be identical. The only difference is that a UNION clause gives you additional keywords to use (UNION ALL, UNION DISTINCT) that create small variations of the basic UNION (OR)

Re: merge two mysql queries

Posted: Sun Oct 31, 2010 9:31 am
by JKM
The thing is that I want to have the first row where deleted=0 and the following rows ordered by deleted DESC.