Click here to Skip to main content
13,629,209 members
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 0:10am
Updated 27-Jun-16 0:52am
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
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!

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 | Cookies | Terms of Service
Web01-2016 | 2.8.180712.1 | Last Updated 27 Jun 2016
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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