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
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response