How to join multiple tables while one table has multiple foreign key fields

Question

0.00/5 (No votes)

See more:

Here are three tables and i want to join them; but bit confused while joining these tables..

Table Name: New_Reg

Reg_ID(PK) old_society_ID(FK) new_society_ID(FK)
1         1                  2

Table Name: Society

Society_ID(PK) Society_Name Center_ID(FK)
1                    AAA          1
2                    BBB          2

Table Name: Center

Center_ID(PK) Center_Name 
1                  Center1
2                  Center2

Finally i want these following output

Reg_ID old_society_ID Old_Society Old_Center new_society_ID New_Society New_Center
1         1              AAA        Center1     2             BBB         Center2

Posted 1-Nov-12 21:17pm

sahabiswarup

Updated 1-Nov-12 21:40pm

Maciej Los

v2

Add a Solution

Comments

sahabiswarup 2-Nov-12 3:43am

Thanks Maciej Los..for modifying

3 solutions

Solution 3

Try this:

SQL

SELECT T1.New_Reg, T1.old_society_ID, T1.Old_Society, T1.Old_Center,
                       T2.new_society_ID, T2.New_Society, T2.New_Center
FROM (SELECT NR.Reg_ID, NR.old_society_ID, S.Society_Name AS Old_Society, C.Center_Name AS Old_Center
	FROM New_Reg AS NR LEFT OUTER JOIN Society AS S ON NR.old_society_ID = S.Society_ID
			LEFT JOIN Center AS C ON S.Center_ID = C.Center_ID ) AS T1
    LEFT JOIN (SELECT NR.Reg_ID, NR.new_society_ID, S.Society_Name AS New_Society, C.Center_Name AS New_Center
				FROM New_Reg AS NR
				LEFT OUTER JOIN Society AS S ON NR.new_society_ID = S.Society_ID
					LEFT JOIN Center AS C ON S.Center_ID = C.Center_ID) AS T2
	ON T1.Reg_ID = T2.Reg_ID

Posted 1-Nov-12 22:45pm

Maciej Los

Comments

sahabiswarup 2-Nov-12 5:15am

Thanks for your support

Solution 1

A bit complext query I suppose with lots of join
But this achieves the result

SQL

select r.reg_id,r.old_society_id,s.society_name,c.center,
r.new_society_id,s1.society_name,c1.center
from reg r left join society s
 on (r.old_society_id = s.society_id) left join society s1
on (r.new_society_id = s1.society_id) left join center c
on(s.center_id = c.center_id) left join center c1
on(s1.center_id = c1.center_id)
where reg_id = 1

Not sure about performace. Some expert can comment probably but this will serve the purpose

Hope that helps. If it does, mark the answer as solution and/or upvote.

Thanks
Milind

Posted 1-Nov-12 22:19pm

MT_

Comments

sahabiswarup 2-Nov-12 5:15am

Thanks for your support

MT_ 2-Nov-12 5:51am

Did you use peft joing as I said or inner join as Vipr@t said or other as Marciej Los said ?

sahabiswarup 5-Nov-12 7:17am

I've tried yours but finally VIPR@T solution works perfectly.

MT_ 5-Nov-12 7:19am

No problem. I tried, what I understood :-)

You able to solve your problem. That's more important.
Cheers
Milind

sahabiswarup 8-Nov-12 1:16am

Thanks Milind for your support..
keep helping
:)

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)

VIPR@T · Accepted Answer · 2012-11-01T22:25:00

Hi,

See the below Query. Might be help you.

SQL

SELECT     New_Reg.Reg_ID, New_Reg.old_society_ID, Society.Society_Name AS Old_Society, Center.Center_Name AS Old_Center, New_Reg.new_society_ID, 
                      Society_1.Society_Name AS New_Society, Center_1.Center_Name AS New_Center
FROM         New_Reg INNER JOIN
                      Society ON New_Reg.old_society_ID = Society.Society_ID INNER JOIN
                      Center ON Society.Center_ID = Center.Center_ID INNER JOIN
                      Society AS Society_1 ON New_Reg.new_society_ID = Society_1.Society_ID INNER JOIN
                      Center AS Center_1 ON Society_1.Center_ID = Center_1.Center_ID

How to join multiple tables while one table has multiple foreign key fields

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us