Result of table of "JOIN"ing two tables including two columns having values using a reference table

Question

0.00/5 (No votes)

See more:

I have three tables including one reference table(including 2 columns Item_IDs with associated Item_names) and two other tables each having a column named ID1,C1 & ID2,C2.
followings are the tables sample structures:

Reference Table:

| item_id | item_name | .....
| 1 | name1 | ....
| 2 | name2 | .....
.
.
Table 1:

| item_id1 | C1 |
| 5 | 10 |
.
.
Table 2:

| item_id2 | C2 |
| 7 | 10 |
.
.
Now, I want to "JOIN" tables Table1 & Table2 where their C1 & C2 columns are equal.
What I want is, in a row of the result table of the above (JOIN) query, I want to have two additional columns one having the item_name of the ID1 of Table1 and one having the item_name of the ID2 of Table2.
For more clarification, below is a sample of the result table(the table that is made of rows in which C1 & C2 of both tables are equal):

| item_id1 | Item_Name1 | item_id2 | Item_Name2 |
| 1 | name1 | 2 | name2 |
.
.
I appreciate your helping me with writing a suitable sql query. Thanks in advance.

Posted 26-Dec-14 2:43am

Member 11033015

Updated 26-Dec-14 3:48am

v3

Add a Solution

Comments

ZurdoDev 26-Dec-14 8:59am

I don't follow. ID1 1 is not in your sample data. ID2 2 is also not in your sample data.

Member 11033015 26-Dec-14 9:31am

Sorry for the mistake..I can't edit the question...consider both "ID1" & "ID2" "item_id".

ZurdoDev 26-Dec-14 9:42am

You should be able to click Improve question.

I guess I'm still confused as to how you are getting the result. You want "name1" and "name2" in the same row as separate columns but they are separate rows in your reference table. I also don't see how Table1 and Table 2 related to reference table.

Member 11033015 26-Dec-14 9:52am

Please re-check the updated column names.
Yes,what you mentioned is exactly what I want...
Table1 `item_id1` column includes numbers(IDs) whose values can be found using reference table.
Table2 `item_id2` column includes numbers(IDs) whose values can be found again using reference table.

1 solution

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)

Tomas Takac · Accepted Answer · 2014-12-27T00:39:00

There is really nothing complicated about it. Join the two tables on C1 & C2 columns. Then join twice to Reference Table to retrieve the item name for each item id. And voila:

SQL

select 
  t1.item_id1, ref1.item_name as item_name1, 
  t2.item_id2, ref2.item_name as item_name2
from 
  table1 t1 inner join table2 t2 on t2.C2 = t1.C1
  inner join ReferenceTable ref1 on ref1.item_id = t1.item_id1
  inner join ReferenceTable ref2 on ref2.item_id = t2.item_id2