Join table based on foreign key

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have two virtual table

table1 fields:B,sum(A)
table2 fields:B,sum(K)

Table1 data:

B sum(A)
1  20
2  30
3  40

Table2 data:

B sum(k)
5  20
6  70
3  20

Join results needed:B,sum(A),sum(K)

B	sum(A)	sum(K)
1	20
2	30
3	40	20
5		20
6		70

Posted 24-May-12 23:46pm

rajin kp

Updated 25-May-12 10:18am

Wendelius

v3

Add a Solution

4 solutions

Solution 1

Actually wat problem u r getting ?

Posted 24-May-12 23:54pm

echosound

Comments

rajin kp 25-May-12 5:59am

If i use inner join matching rows selected
i need result as
B sum(A) sum(K)
1 20
2 30
3 40 20
5 20
6 70

Maciej Los 25-May-12 6:23am

This is not an anser! Please, delete it. If you have any question or comment, use "Have a Question or Comment" button.

Solution 3

I think you need to use 'Union' or 'Union all' for this result

Posted 25-May-12 0:01am

madhusudhan.k

Solution 4

use Full Outer Join so that u can get match and non-match rows

SQL

select A.Sum(A),b.Sum(K) from Table1 As A full outer join Table as B on A.B=B.B

Posted 25-May-12 0:06am

echosound

Updated 25-May-12 10:19am

v3

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)

Manas Bhardwaj · Accepted Answer · 2012-05-25T00:00:00

Solution 2

SQL

SELECT
    CASE WHEN T1.B IS NULL THEN T2.B ELSE T1.B END AS B,
    A,
    K
FROM
    Table_1 AS T1 FULL JOIN Table_2 AS T2
    ON T1.B = t2.B

I would suggest you read JOIN [^]fundamentals.

Posted 25-May-12 0:00am

Manas Bhardwaj

Comments

rajin kp 25-May-12 6:17am

thanks Manas Bhardwaj

Maciej Los 25-May-12 6:22am

Good work, Manas. +5!
I see that you are using aliases ;)

Manas Bhardwaj 25-May-12 6:43am

Thanks!
Yeah, Always open to good ideas :)