Click here to Skip to main content
13,406,177 members (51,227 online)
Rate this:
 
Please Sign up or sign in to vote.
First table
EMPID	CODEID	amount
0157	C	0
0157	F	0
0157	L	0
0157	M	0
0157	T	0
0737	C	0
0737	F	0
0737	L	0
0737	M	0
0737	T	0

second table
EMPID	CODEID	TOTAL_PAID_AMT
0157	T	4174.10
0737	C	5565.93


How to join these two tables such that second table has 5 rows because first has 5 rows.
Only one non zero values needs to be in second table non-matching rows should be zero.
There are 5 code id for each empid so respective 5 rows needs to displayed in final o/p.
If some amount is present in 2nd table then it should be shown other wise 0 needs to be displayed.

What I have tried:

select * from first f, second s
where f.empid=s.empid
and f.codeid=s.codeid
order by 1
Posted 27-Jun-16 1:10am
Updated 27-Jun-16 1:52am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try:
SELECT f.EMPID, 
       f.CODEID, 
       ISNULL(s.TOTAL_PAID_AMT, 0) 
FROM FirstTable f
LEFT JOIN SecondTable s 
ON f.EMPID = s.EMPID AND f.CODEID = s.CODEID
  Permalink  
Comments
Maciej Los 27-Jun-16 6:53am
   
5ed!
shardul shende 27-Jun-16 8:53am
   
Tried the solution but its not working.@originalGriff
OriginalGriff 27-Jun-16 8:56am
   
Show us exactly the query you used, and exactly what it produced for your data sample in the original question.

"it's not working" helps no-one. It doesn't tell us anything about what it does that you didn't expect, or doesn't do that you did!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

In addition to solution1 by OriginalGriff[^], i'd strongly recommend to read this article: Visual Representation of SQL Joins[^]
  Permalink  
Comments
Karthik Bangalore 27-Jun-16 8:16am
   
5ed!
much recommended article
Maciej Los 27-Jun-16 8:18am
   
Thank you, Karthik.
mahadevkarekar 27-Jun-16 8:54am
   
The article is extremely helpful!
Thanks for sharing this article Maciej Los
Maciej Los 27-Jun-16 9:09am
   
You're very welcome ;)
0x01AA 28-Jun-16 16:12pm
   
My favorite article, a 5.
Bruno
Maciej Los 28-Jun-16 16:49pm
   
Mein auch ;)
Thank you, Bruno.
0x01AA 28-Jun-16 16:52pm
   
Moje ulubione produkty... ok it was Google translate not me :-)
Bruno
Maciej Los 28-Jun-16 16:55pm
   
Well, Google didn't get proper translation. This means: "My favorite items/products"
Cheers!
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.180221.1 | Last Updated 27 Jun 2016
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100