a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

a join

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-12 : 22:39:00

Hi everyone...getting trouble in sql statement again.

table 1: hrEmpLoanledger
pkeyno lamount fixdeduc
1 100000 100
2 100000 200
3 100000 300
4 100000 400
5 100000 500

table2: hrEmpLoanPayment
fkeyno pamount
1 50000
4 100000
5 100000

My expected result:
pkeyno pamount
1 0
2 0
3 50000

Thanks --

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-12 : 22:43:06

What's the logical here?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 22:43:53

can you kindly explain how to obtain the result ?

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-12 : 22:47:15

ooops sorry my fault.

my expected result is:

pkeyno pamount
1 50000
2 0
3 0

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 22:56:15

this ?


declare @hrEmpLoanledger table
(
	pkeyno 		int,
	lamount 	int,
	fixdeduc	int
)
insert into @hrEmpLoanledger
select	1, 100000, 100	union all
select	2, 100000, 200	union all
select	3, 100000, 300	union all
select	4, 100000, 400	union all
select	5, 100000, 500

declare @hrEmpLoanPayment table
(
	fkeyno 		int,
	pamount		int
)
insert into @hrEmpLoanPayment
select	1, 50000	union all
select	4, 100000	union all
select	5, 100000

select	l.pkeyno, p.pamount
from	@hrEmpLoanledger l left join @hrEmpLoanPayment p
	on	l.pkeyno	= p.fkeyno
where	p.fkeyno	is null
or	l.lamount 	<> p.pamount

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-12 : 23:05:35

Yes it is.
Thanks again KH.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources