How to Fixed this MySql Query Bandi and Ranjair2015

Posted by Jayakumars under ADO.NET on 8/4/2016 | Points: 10 | Views : 242 | Status : [Member] [MVP] | Replies : 5
Hi

I need MySql Query Issue (Most Urgent) any one guide me.

this is table with sample Data.

CREATE  TABLE `test`.`mas` (

`id` INT NOT NULL AUTO_INCREMENT ,

`Name` VARCHAR(45) NULL ,

`Balance` DECIMAL(18,2) NULL ,

`EmpNo` INT NULL ,

PRIMARY KEY (`id`) );




CREATE TABLE `test`.`trans` (

`id` INT NOT NULL AUTO_INCREMENT ,

`EmpNo` INT NULL ,

`Recept` DECIMAL(18,2) NULL ,

`Payment` DECIMAL(18,2) NULL ,

`Status` VARCHAR(45) NULL ,

`Date` DATE NULL ,

PRIMARY KEY (`id`) );




INSERT INTO `test`.`mas` (`Name`, `Balance`, `EmpNo`) VALUES ('John', '1500', 1001);

INSERT INTO `test`.`mas` (`Name`, `Balance`, `EmpNo`) VALUES ('Wesley', '2500', 1002);

INSERT INTO `test`.`mas` (`Name`, `Balance`, `EmpNo`) VALUES ('Kilbert', '3500', 1003);

INSERT INTO `test`.`mas` (`Name`, `Balance`, `EmpNo`) VALUES ('Rajas', '4500', 1004);





INSERT INTO `test`.`trans` (`EmpNo`, `Recept`, `Payment`, `Status`, `Date`) VALUES (1001, '120', '130', 'ACTIVE', '2015-02-01');

INSERT INTO `test`.`trans` (`EmpNo`, `Recept`, `Payment`, `Status`, `Date`) VALUES (1001, '130', '150', 'REJECT', '2015-04-04');

INSERT INTO `test`.`trans` (`EmpNo`, `Recept`, `Payment`, `Status`, `Date`) VALUES (1001, '180', '140', 'ACTIVE', '2015-06-06');

INSERT INTO `test`.`trans` (`EmpNo`, `Recept`, `Payment`, `Status`, `Date`) VALUES (1002, '250', '250', 'ACTIVE', '2015-03-03');

INSERT INTO `test`.`trans` (`EmpNo`, `Recept`, `Payment`, `Status`, `Date`) VALUES (1004, '320', '321', 'REJECT', '2015-04-04');



I need Following Condition Output.

1.Need Left Join mas and trans table for match or not in empno
2.when the record mas and trans presence i need check status active records only shown.
3.opening balance calculation like this
before from date which we entered sum following field mas table Balance+Recept-Payment.

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 8/4/2016 [Member] [MVP] Platinum | Points: 25

Up
1
Down
Hi Jayakumars,

It would help me to provide proper solution if you post the expected results from your sample data

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhuvanesh6 on: 8/4/2016 [Member] Starter | Points: 25

Up
0
Down
I have provided a sample query to Join Table and also calculate values of column, modify accordingly.

 select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
join users on groups.owner=users.id
join items on items.group=groups.id
join content on content.id=items.content
group by groups.name


Bhuvan

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Webmaster on: 8/4/2016 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
@Bhuvanesh6,

Thanks for reply and helping the author of this question. Much appreciated! Your solution may or may not help the person who has asked question. It would be nice to write to-the-point answer if possible.

Thanks

Best regards,
Webmaster
http://www.dotnetfunda.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 8/4/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Let's address your problem in steps

1.Need Left Join mas and trans table for match or not in empno

SELECT m.*,t.*
FROM mas m
LEFT JOIN trans t ON m.EmpNo =t.EmpNo

/* Result
-----------
id Name Balance EmpNo id EmpNo Recept Payment Status Date
---- ---- ------- ----- --- ----- ------ ------- ------ -----
1 John 1500.00 1001 1 1001 120.00 130.00 ACTIVE 2015-02-01
1 John 1500.00 1001 2 1001 130.00 150.00 REJECT 2015-04-04
1 John 1500.00 1001 3 1001 180.00 140.00 ACTIVE 2015-06-06
2 Wesley 2500.00 1002 4 1002 250.00 250.00 ACTIVE 2015-03-03
3 Kilbert 3500.00 1003 NULL NULL NULL NULL NULL NULL
4 Rajas 4500.00 1004 5 1004 320.00 321.00 REJECT 2015-04-04

*/


2.when the record mas and trans presence i need check status active records only shown.

SELECT m.*,t.*
FROM mas m
LEFT JOIN trans t ON m.EmpNo =t.EmpNo
WHERE t.Status = 'ACTIVE'

/* Result
-----------
id Name Balance EmpNo id EmpNo Recept Payment Status Date
---- ---- ------- ----- --- ----- ------ ------- ------ -----
1 John 1500.00 1001 1 1001 120.00 130.00 ACTIVE 2015-02-01
1 John 1500.00 1001 3 1001 180.00 140.00 ACTIVE 2015-06-06
2 Wesley 2500.00 1002 4 1002 250.00 250.00 ACTIVE 2015-03-03

*/



3.opening balance calculation like this before from date which we entered sum following field mas table Balance+Recept-Payment.

SELECT m.EmpNo,m.Name,SUM(m.Balance) + SUM(t.Recept) - SUM(t.Payment) AS Total
FROM mas m
LEFT JOIN trans t ON m.EmpNo =t.EmpNo
WHERE t.Status = 'ACTIVE'
GROUP BY m.EmpNo,m.Name

/* Result
-----------
EmpNo Name Total
---- ---- ------
1001 John 3030.00
1002 Wesley 2500.00

*/


Kindly let us know if that helps.

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/5/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi

I provide everything here can you fixed it


I have master and Detail Table here how to match sum and total value i metion below my output.

CREATE  TABLE `test`.`mastable` (


`Id` INT NOT NULL AUTO_INCREMENT ,

`EmpNo` INT NULL ,

`Name` VARCHAR(45) NULL ,

`Balance` DECIMAL(6,2) NULL ,

PRIMARY KEY (`Id`) );


CREATE TABLE `test`.`transtable` (

`Id` INT NOT NULL AUTO_INCREMENT ,

`EmpNo` INT NULL ,

`Date` DATE NULL ,

`Receipt` DECIMAL(6,2) NULL ,

`Payment` DECIMAL(6,2) NULL ,

PRIMARY KEY (`Id`) );


INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (10, 'David', '1000');

INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (20, 'Miller', '300');

INSERT INTO `test`.`mastable` (`EmpNo`, `Name`, `Balance`) VALUES (30, 'Warner', '0');




INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-04-05', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-05-07', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-06-08', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-07-10', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-08-11', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Payment`) VALUES (10, '2015-09-12', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-10-12', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-10-14', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-11-15', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2015-12-17', '200');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-01-18', '100');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-02-10', '500');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Payment`) VALUES (10, '2016-02-19', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-03-22', '300');

INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (10, '2016-03-31', '500');


INSERT INTO `test`.`transtable` (`EmpNo`, `Date`, `Receipt`) VALUES (20, '2016-04-22', '1000');





I need Output this
===================
Shareno Name OB Recept Payment SumRecept OB sumpaymt Temp CB
10 David 1000 2700 300 800 1800 500 1300 4200
20 Miller calculate forumla above record

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response