I need output data following Concept

Posted by Jayakumars under Sql Server on 7/31/2016 | Points: 10 | Views : 1484 | Status : [Member] [MVP] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I need follow this Sql server and Mysql

Mysql is better

create table mas
(
id int primary key identity(1,1),
Name varchar(30),
amount1 decimal(18,2)
)


create table trans
(
id int primary key identity(1,1),
NameID int,
amount1 decimal(18,2),
amount2 decimal(18,2),
amount3 decimal(18,2)
)

INSERT INTO MAS VALUES('John',1000.35);
INSERT INTO MAS VALUES('John seena',2000.35);
INSERT INTO MAS VALUES('John wesly',3000.35);
INSERT INTO MAS VALUES('John Gilbert',4000.35);
INSERT INTO MAS VALUES('John sherlok',5000.35);


select * from mas
select * from trans
insert into trans values(1,101,201,301)
insert into trans values(1,111,211,311)
insert into trans values(3,301,401,501)
insert into trans values(5,11,21,31)
insert into trans values(5,21,31,41)

--here i need using left join mas and trans records match or not i need shows records so i use left join

--my query this

SELECT * FROM MAS
SELECT * FROM TRANS

Both Query Not Working... any one fixed this?

--Query need I

SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1)+SUM(B.AMOUNT2)-SUM(B.AMOUNT3)

FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID

--Query need II

SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1),SUM(B.AMOUNT2),SUM(B.AMOUNT3)

FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	SELECT A.ID,NAME,A.AMOUNT1,SUM(ISNULL(B.AMOUNT1,0))+SUM(ISNULL(B.AMOUNT2,0))-SUM(ISNULL(B.AMOUNT3,0)) FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID GROUP BY A.ID,NAME,A.AMOUNT1 /* ID NAME AMOUNT1 (No column name) 1 John 1000.35 12.00 2 John seena 2000.35 0.00 3 John wesly 3000.35 201.00 4 John Gilbert 4000.35 0.00 5 John sherlok 5000.35 12.00 / 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

SELECT A.ID,NAME,A.AMOUNT1,SUM(ISNULL(B.AMOUNT1,0))+SUM(ISNULL(B.AMOUNT2,0))-SUM(ISNULL(B.AMOUNT3,0))
FROM MAS A 
LEFT JOIN TRANS B 
ON A.ID=B.NAMEID
GROUP BY  A.ID,NAME,A.AMOUNT1

/*
ID	NAME		AMOUNT1		(No column name)
1	John		1000.35		12.00
2	John seena	2000.35		0.00
3	John wesly	3000.35		201.00
4	John Gilbert	4000.35		0.00
5	John sherlok	5000.35		12.00
*/

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: Rajnilari2015 on: 8/1/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

0	Where is the group by??? Query 1 SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1)+SUM(B.AMOUNT2)-SUM(B.AMOUNT3) FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID GROUP BY A.ID,NAME,A.AMOUNT1 Query 2 SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1),SUM(B.AMOUNT2),SUM(B.AMOUNT3) FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID GROUP BY A.ID,NAME,A.AMOUNT1 Hope that helps -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Where is the group by???

Query 1

SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1)+SUM(B.AMOUNT2)-SUM(B.AMOUNT3)
FROM MAS A 
LEFT JOIN TRANS B 
ON A.ID=B.NAMEID
GROUP BY  A.ID,NAME,A.AMOUNT1

Query 2

SELECT A.ID,NAME,A.AMOUNT1,SUM(B.AMOUNT1),SUM(B.AMOUNT2),SUM(B.AMOUNT3)
FROM MAS A LEFT JOIN TRANS B ON A.ID=B.NAMEID
GROUP BY  A.ID,NAME,A.AMOUNT1

Hope that helps

--
Thanks & Regards,
RNA Team

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

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

0	hi i write MySql Query like this select s.shareno,s.date as Dat,s.nameE,t.InstNameE, Recptamt,Payamt , t.oldbalance as bal, t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) as opnbal , t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0)+Recptamt-Payamt + COALESCE((SELECT ifnull(SUM(Recptamt-Payamt),0)))as Total, t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) opnbal1 from `dbbank`.`tblsharemaster` as t left join `dbbank`.`sharledg` as s on s.shareno=t.shareno; But sharemaster have 3 or more records also sharledg 5 records My need left join usage is match or not all records shows ? but in this query output coming one record only this is wrong. I need all records need to come becos i use left join so. how to solve this Can you fixed Bani and Rajanaluri Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

hi

i write MySql Query like this

select s.shareno,s.date as Dat,s.nameE,t.InstNameE,

Recptamt,Payamt , t.oldbalance as bal,

t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) as opnbal ,

t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0)+Recptamt-Payamt 

+ COALESCE((SELECT ifnull(SUM(Recptamt-Payamt),0)))as Total,

t.oldbalance + ifnull(sum(recptamt)-sum(Payamt),0) opnbal1

from `dbbank`.`tblsharemaster` as t 

left join `dbbank`.`sharledg` as s on s.shareno=t.shareno;

But sharemaster have 3 or more records also sharledg 5 records
My need left join usage is match or not all records shows ?

but in this query output coming one record only this is wrong.

I need all records need to come becos i use left join so.

how to solve this

Can you fixed Bani and Rajanaluri

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Latest Posts