[SOLVED] MySQL UNION: Only the first select statement returns a result.

[ridiculous]

Hello. I am attempting to join multiple select statements into a single query.

However, I am not able to extract the result of any of the UNIONED

selects.

 

 

 

 

 

 

 

 

 

$query1 = mysql_query ("
			(
			SELECT 		SUM(deposits) 		AS total_deposits
			FROM table1
			)

	UNION ALL
			(
			SELECT 		SUM(withdrawals) 	AS total_withdrawals
		        FROM table1
			)
		") 
	or die (mysql_error());

$result1 = mysql_fetch_array($query1); 
extract($result1); 

 

 

When I echo the results of the second select statement I get nothing.

 

 

echo  $total_withdrawals;

 

 

[dough boy]

Try:

 

SELECT SUM(deposits) AS deposits, w.withdrawls FROM table1, (SELECT SUM(withdrawls) AS withdrawls FROM table1) AS w

[ridiculous]

Thanks, but I don't think your code came through in its entirety.

 

 

[dough boy]

Nope. That is the query that you should run.  It should return 2 fields, deposits, and withdrawls.  What happens when you run it?

[ridiculous]

Darn.

 

When I echo the first variable (deposits), I get a result.

When I echo the second variable (withdrawals), I get nothing.

 

When I reverse the order of the code, I receive the same result. I'm running MySQL 5 and PHP 5.

 

Any thoughts on a test I could run? Its definitely in the way I've formed the UNION.

[dough boy]

try print_r($result1) and what do you get?

[btherl]

Since both queries are on the same table, you should be able to use this:

 

SELECT SUM(deposits) AS total_deposits, SUM(withdrawals) AS total_withdrawals FROM table1

 

There's no need for unions or subqueries.  Union is for a different situation - it's for when you have some rows from table 1 and some more rows from table2 which look the same, and you want to merge them into one huge blob of rows.

[ridiculous]

That's really interesting. Here's what I get:

 

Array
(
    [0] => 7025.23
    [total_deposits] => 7025.23
)

 

 

Pretty cool trick, that print idea. Now I'm just wondering how to analyze this result.

[dough boy]

btherl is correct.  What I gave you is if you have 2 different tables.

[ridiculous]

Thanks guys.

 

Since my usage was all wrong, would you guys have any advice on how to join these separate select statements into one mysql_query?

 

Here is my code.

 

 

$query1 = mysql_query ("
(
SELECT 		SUM(deposits) 		AS total_deposits									
)

					UNION ALL
(
SELECT 		SUM(withdrawals) 	AS total_withdrawals
)

					UNION ALL
(
SELECT 		EOD_Equity 			AS EOD_Equity_at_inception
WHERE record=(Select MIN(record) 
from superfund_historicals)
)

					UNION ALL
(
SELECT 		EOD_Equity 			AS  EOD_Equity_now
WHERE record = (select Max(record) 
from superfund_historicals)
)

					UNION ALL

(
SELECT 		EOD_Equity 			AS  EOD_Equity_tminus1	
WHERE record = (select Max(record)-1 
from superfund_historicals)
)



					") 
					or die (mysql_error());

$result1 = mysql_fetch_array($query1); //get a row from our result set
extract($result1); 

 

 

[dough boy]

If they share the same common id, then 1 query should be able to do it.  But if you have 2 tables, 1 for deposits, and 1 for withdrawls, then you should theoretically have to do what I gave you.

[ridiculous]

Ok I'm working on it. I'll post what I come up with and see what you think.

[ridiculous]

Here's what I came up with. I get an error in the 3rd paragraph:

 

 

EOD_Equity AS EOD_Equity_at_inception

WHERE record=(Select MIN(record)

from superfund_historicals),

 

$query1 = mysql_query ("

SELECT 		    
                        SUM(deposits) 		AS total_deposits,						

		SUM(withdrawals) 	AS total_withdrawals,

---->		        EOD_Equity 			AS EOD_Equity_at_inception
---->			WHERE record=(Select MIN(record) 
---->			from superfund_historicals),

		EOD_Equity 			AS  EOD_Equity_now
		WHERE record = (select Max(record) 
		from superfund_historicals),

		EOD_Equity 			AS  EOD_Equity_tminus1	
		WHERE record = (select Max(record)-1 
		from superfund_historicals)

		FROM superfund_historicals




					") 
					or die (mysql_error());

$result1 = mysql_fetch_array($query1); //get a row from our result set
extract($result1); 

 

 

 

Of course, it does work just fine for this:

 

 

$query1 = mysql_query ("

SELECT 	    SUM(deposits) 	      AS total_deposits,								
	SUM(withdrawals) 	AS total_withdrawals,

	FROM superfund_historicals

			") 
			or die (mysql_error());

$result1 = mysql_fetch_array($query1); //get a row from our result set
extract($result1); 

...but I've got to use a where statement.

[ridiculous]

I also tried this, but it doesn't seem to work:

 

$query1 = mysql_query ("

SELECT 		SUM(deposits) 		AS total_deposits
		FROM superfund_historicals,									

		SUM(withdrawals) 	AS total_withdrawals
		FROM superfund_historicals,

		EOD_Equity 			AS EOD_Equity_at_inception
		FROM superfund_historicals
		WHERE record=(Select MIN(record) 
		from superfund_historicals),

		EOD_Equity 			AS  EOD_Equity_now
		FROM superfund_historicals
		WHERE record = (select Max(record) 
		from superfund_historicals),

		EOD_Equity 			AS  EOD_Equity_tminus1
		FROM superfund_historicals	
		WHERE record = (select Max(record)-1 
		from superfund_historicals)		 					

					") 
					or die (mysql_error());

$result1 = mysql_fetch_array($query1); //get a row from our result set
extract($result1); 

[btherl]

Can I ask the obvious question.. why are you merging your queries instead of using seperate queries?

 

The difficulty in merging the queries you've given are that each one has different "where" conditions.  And they have outputs of a different nature as well.  Given those two facts, I don't see why you would want to merge them.

 

The other argument against merging the queries is that you would replace several simple queries with one complex one.  A complex query is much more difficult to debug.

[ridiculous]

To be honest, I've learned PHP in bits and pieces...and looking at my 5 separate MySQL queries  simply didn't look right.

 

 

Also, I am under the impression that running a bunch of separate queries is less desirable than bundling them into one mysql_query call.

 

 

Tell me if I'm wrong.

 

 

$query1 = mysql_query ("SELECT 		SUM(deposits) AS totalin,
								SUM(withdrawals) AS totalout
								FROM superfund_historicals
					;") 
					or die (mysql_error());

$result1 = mysql_fetch_array($query1); //get a row from our result set
extract($result1); 
$netdeposits = $totalin - $totalout;
//
//
//
//-----------------EQUITY OF COMPOSITE FUND AT INCEPTION  ($epoch)
$query2 = mysql_query ("SELECT 		* FROM superfund_historicals 
								WHERE record=(Select MIN(record) from 			
								superfund_historicals)
					;")
				    or die (mysql_error());

$result2 = mysql_fetch_array($query2); //get a row from our result set
extract($result2);
$epoch = $EOD_equity;
//
//
//
//-----------------EQUITY OF FUND AT END OF TODAY INCLUDES YESTERDAYS DEPOSITS
//				   WITHDRAWALS BUT BACKS OUT THOSE FROM TODAY ($t0)
$query3 =mysql_query 	("SELECT 
			* 
	FROM 
			superfund_historicals 		
	WHERE
			record = (select Max(record) from superfund_historicals);")
	or die (mysql_error()); 

$result3 = mysql_fetch_array($query3); //get a row from our result set
extract($result3); 

$t0=$EOD_equity-$deposits+$withdrawals;
$date_of_t0 = $date; 
//
//
//
//-----------------EQUITY OF FUND AT END OF YESTERDAY/INCLUDES DEPOSITS,WITHDRAWALS ($tminus1)
$query4 =mysql_query ("SELECT 
			* 
	FROM 
			superfund_historicals 		
	WHERE
			record = (select Max(record)-1 from superfund_historicals);")
	or die (mysql_error()); 

$result4 = mysql_fetch_array($query4); 
extract($result4); 

 

 

 

It just looks wrong. Would you do your code like this?

 

 

 

 

 

 

[btherl]

It depends what you mean by "less desirable"

 

A single query is probably more efficient.  Multiple queries are more simple.

 

When given a choice between efficiency and simplicity, I almost always go for simplicity.  Simple code means fewer bugs, and if you're dealing with peoples' investments, less bugs is VERY important.  Much more important than having your code run a little bit faster.

 

The way the code is written in your latest post is how I would do it.  Every query is accompanied by a description of what it does, and every query is simple to understand.  That to me is very good code.

 

There is one situation that I would change it to a single query, and that is if the system runs too slowly AND it is not feasible to purchase better hardware.  It also depends on the cost of your time vs the cost of better hardware.  If your time is cheap, then by all means spend more time optimizing the code and more time maintaining the optimized query.  But if your time is valuable, you may find it cheaper to buy a faster machine and leave the code as it is.

[ridiculous]

Frankly, I'm surprised by your response. I am not too familiar with putting SELECT statements together and have been very surprised to see that I can't call something like

 

$unified_query = mysql_query ("SELECT 		

----> FROM TABLE


(EOD_equity 	AS starting_equity

		WHERE record=(Select MIN(record) from 			
		superfund_historicals)),

(date 			AS date_t_0,
EOD_equity  	     AS t_0,
deposits		AS daily_deposits,
withdrawals	       AS daily_withdrawals 		
		WHERE record = (select Max(record) from 					
		superfund_historicals))

					;")
				    or die (mysql_error());

$result2 = mysql_fetch_array($query2); //get a row from our result set
extract($result2);

 

 

Or something to this effect, where you simple call multiple selects on the same table (but with results having different columns) under one mysql_query.

 

 

I guess you can't, because I haven't found documentation to this affect anywhere.

[ridiculous]

Nevertheless, thanks for all the help guys. I really appreciate it.

[btherl]

It's not really something you would want to do normally.  If you want to combine related data together then you can use joins.  And if you want to combine similar result sets into one result set you would use union.  But combining several unrelated (in the sense of how they are calculated) data items into one result set is just not "logical" in SQL.

 

You an look at an SQL query this way.  It starts with an entire table or tables:

 

-------

-------

-------

 

Then it uses the "where" condition to pick certain elements from that

 

-    -

    -

  -

 

Then it either returns those items as they are (as many rows), or aggregates them, such as taking a sum or count.

 

When you have different "where" conditions in one query, there's no natural way for SQL to deal with that.  Usually it chooses items based on the "where", and then operates on them.  But if you have different conditions for each of your different outputs, then which conditions does it use?  Or does it use each condition in turn, starting from the full set of data each time (which is basically the same as doing individual queries) ?

 

So you might as well use several queries, each with its own "where" condition.