How to join two tables to get this output

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

SQL-Server-2012

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

shardul shende

Updated 27-Jun-16 0:52am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2016-06-27T00:52:00

Solution 2

In addition to solution1 by OriginalGriff[^], i'd strongly recommend to read this article: Visual Representation of SQL Joins[^]

Posted 27-Jun-16 0:52am

Maciej Los

Comments

Karthik_Mahalingam 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 ;)

[no name] 28-Jun-16 16:12pm

My favorite article, a 5.
Bruno

Maciej Los 28-Jun-16 16:49pm

Mein auch ;)
Thank you, Bruno.

[no name] 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

OriginalGriff · Accepted Answer · 2016-06-27T00:25:00

Solution 1

Try:

SQL

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

Posted 27-Jun-16 0:25am

OriginalGriff

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!