Join Problem in sql ...

Question

1.00/5 (1 vote)

See more:

i have two table Like table A and Table B As

  Table A                   Table B   
                      
PiD     Val               FID    VAL2
 1       A                 2      E
 2       B                 3      M 
 3       C                 6      L
 4       D                 7      P
 5       E

I want a table with he help of SQL statement Like

Table C

Pid   Val        Val2
 1     A          Null
 2     B          E
 3     C          M
 4     D          Null
 5     E          Null
 6     Null       L
 7     Null       P

Please reply soon...

Posted 27-Dec-13 8:44am

Arpana Mallik

Updated 27-Dec-13 10:09am

Nelek

v3

Add a Solution

Comments

CHill60 27-Dec-13 14:46pm

What have you tried?

ZurdoDev 27-Dec-13 14:50pm

I don't follow. What is the output you are expecting?

3 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)

gettgotcha · Answer 1 · 2013-12-27T09:19:00

Solution 3

SQL

SELECT COALESCE(Pid, FID) as Pid, Val, Val2
FROM A
FULL JOIN B ON A.Pid=B.FID

This will give you the expected result, as you need both rows from two tables then you need to use Full Join instead of Left (or Right) Outer Joins.

For further reference please go through
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

Posted 27-Dec-13 9:19am

gettgotcha

Updated 27-Dec-13 9:20am

v2

Comments

Christian Graus 27-Dec-13 15:23pm

You're right, I missed that, and I bet that's why he was confused, he probably was trying left joins.

CHill60 27-Dec-13 16:10pm

Yep I missed those last two lines in the results too. 5'd. Next time I'll format the question first ;-p

CHill60 · Answer 2 · 2013-12-27T08:52:00

Solution 1

SQL

SELECT Pid, Val, Val2
FROM A
LEFT OUTER JOIN B ON A.Pid=B.FID

This is very simple stuff so you might find this link useful too
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html[^]

Posted 27-Dec-13 8:52am

CHill60

Christian Graus · Answer 3 · 2013-12-27T09:04:00

Just to add, an inner join means 'only return values that match both tables'. A left outer join means 'return all the rows to the left of me, and any that don't have values in the table on the right, return null'. A full join returns all values from both tables, returning nulls for missing rows.

http://www.codeproject.com/Articles/700317/SQL-Wizardry-Episode-One-Joins[^] is my article on SQL joins.