Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 3106
Home > Forums > ADO.NET >

How to Fixed this MySql Query Bandi and Ranjair2015

Posted by Jayakumars under ADO.NET on 8/4/2016 | Points: 10 | Views : 1785 | Status : [Member] [MVP] | Replies : 5
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment
Alert Moderator 

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

Kumaraspcode2009@gmail.com

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

Login to post response

Latest Posts