Select Data from two mysql tables using one query not working properly

[Sashika_1]

I want to retrive data from two tables. I used below code :

(SELECT sum(total_amount) as tr from loan where loan_status = 'not finish') 
union 
(SELECT sum(amount) as tt from settlement where sett_status = 'Active')

But It load data by one column and these two values load under the another values
I want to recieve those values to two columns...
Becuase I want to retrive these to vb.net variable for make chart.

[cereal]

Hello,

you can use a stored procedure, for example:

DROP PROCEDURE IF EXISTS `calcTotals`;
DELIMITER //
CREATE PROCEDURE `calcTotals`(IN `loanStatus` VARCHAR(100), IN `settlementStatus` VARCHAR(100))
BEGIN
    DECLARE `total_loan` DECIMAL(10,2);
    DECLARE `total_settlement` DECIMAL(10,2);
    SELECT SUM(`total_amount`) INTO `total_loan` FROM `loan` WHERE `loan_status` = `loanStatus`;
    SELECT SUM(`amount`) INTO `total_settlement` FROM `settlement` WHERE `sett_status` = `settlementStatus`;
    SELECT `total_loan`, `total_settlement`;
END
//
DELIMITER ;

And then run it like this:

CALL calcTotals('not finish', 'Active');

It will return something like:

+------------+------------------+
| total_loan | total_settlement |
+------------+------------------+
|     550.00 |            75.00 |
+------------+------------------+
1 row in set (0.00 sec)

[Santanu.Das]

You can try this statement

Select A.tr, B.tt From (SELECT sum(total_amount) as tr from loan where loan_status = 'not finish') A,(SELECT sum(amount) as tt from settlement where sett_status = 'Active') B

[john_111]

A union is intended to produce all the results as if they were in one table
so for tableA {x,y,z,a,b,c} and tableB{p,q,r,s,t} , a union that asked for columns x,y,x from tableA and union with columns q,r,s for tableB would return the data x,y,z followed by q,r,s below it. That is what you say you got.

The restrictions on a union are that you must extract the same number of columns from each table and they must have the same datatype ie datatype x same as datatype q, datatype y same as datatype r and datatype z same as datatype s. So you might have to re-arrange the order if, say datatype x matched datatype r, and datatype y matched datatype q. So then you'd do the union on x,y,z and r,q,s .

results would look like this for the simpler first example
x1,y1,z1
x2,y2,z2
x3,y3,z3
q1,r1,s1
q2,r2,,s1

Does this make things clearer?

[Santanu.Das]

Union Clause
Multiple queries can be put together and their output can be combined using the union clause. The union clause merges the output the output of two or more quries into a single set of rows and columns.

Optput = Records from query 1 + Records from query 2 + a single set of records, common in both queries.

While working with the UNION clause the following pointers should be considered:
1) The number of columns and the data types of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
2) UNION operates over all of the columns being selected.
3) NULL values are not ignored during duplicate checking.
4) The IN operator has a higher precedence than the UNION operator.
5) By default, the output is sorted in ascending order of the first column of the SELECT clause.

The restriction on using a union are as follows:
1) Number of columns in all the quries should be the same.
2) The data type of the columns in each query must be same.
3) Unions cannot be used in subquries.
4) Aggregate function cacot be used within union clause.

Your statement should be

select tt from (
    select sum(total_amount) As tt from loan where loan_status = 'not finish' union all
    select sum(amount) As tt from settlement where sett_status = 'Active') x 

Hope it can help you.