I need output following Concept [Resolved]

Posted by Jayakumars under Sql Server on 7/31/2016 | Points: 10 | Views : 320 | Status : [Member] [MVP] | Replies : 2
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


Responses

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

Up
0
Down

Resolved
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

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

Login to post response