How to Fix a MySQL Query?

Question

CREATE TABLE test.TableOne (

Id INT NOT NULL AUTO_INCREMENT ,

Empno INT NULL ,

Name VARCHAR(45) NULL ,

Balance DECIMAL(6,2) NULL ,

Place VARCHAR(45) NULL ,

PRIMARY KEY (Id) );

CREATE TABLE test.TableTwo (

Id INT NOT NULL AUTO_INCREMENT ,

Date DATE NULL ,

Empno INT NULL ,

Receipt DECIMAL(6,2) NULL ,

Payment DECIMAL(6,2) NULL ,

Status VARCHAR(45) NULL ,

PRIMARY KEY (Id) );

INSERT INTO test.tableone (Empno, Name, Balance, Place) VALUES (100, 'John', '1500', 'Wasinton DC');

INSERT INTO test.tableone (Empno, Name, Balance, Place) VALUES (101, 'Joselin', '1000', 'Dexcity');

INSERT INTO test.tableone (Empno, Name, Balance, Place) VALUES (102, 'Rusfal', '0', 'Donxes'); INSERT INTO test.tableone (Empno, Name, Balance, Place) VALUES (103, 'Raser', '100', 'versity');

INSERT INTO test.tableone (Empno, Name, Balance, Place) VALUES (104, 'rse', '2500', 'sew');

INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-15', 100, '1000', '0', 'OK'); INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-15', 100, '0', '1000', 'OK');

INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-17', 101, '0', '2000', 'OK');

INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-18', 103, '100', '0', 'NOT OK'); INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-19', 100, '1500', '0', 'OK'); INSERT INTO test.TableTwo (Date, Empno, Receipt, Payment, Status) VALUES ('2016-08-20', 100, '0', '1000', 'OK');

Then I need Output this

starting date '2016-08-18' ending date '2016-08-20'

Empno Name Place OB CB
100 John Wasinton DC 2400 2000 101 Joselin Dexcity 1000 0 102 Rusfal Donxes 0 0

etc 103 104

also

Condition OB less amount get less than starting Date based on status OK Only

forumula OB=Balance+Receipt-Payment

Condition CB calculation from date to date based on status OK Only

forumula CB=Balance+Receipt-Payment

Answer 1

Use this:

SELECT a.id,a.name,sum(amount1)as amount1,sum(Receipt) as Receipt,sum(payment) as payment,
sum(amount1)+sum(Receipt)-sum(payment) as Total,nameid,b.name 
FROM test.mas as a left join test.trans as b on a.id=b.nameid GROUP BY a.id;