Joining three tables with different column name?

[jerryisgood]

Is there a way to join three tables that do not have a common column name? I have three tables..

 

Table 1: Main data  => This table has an "id" that auto increments..

 

Tables 2 and 3: Other datas => This has an "id" but it does NOT match the id of table one..However, it has a tmpID field that does match. This is because these two tables are related to fields that can be expanded and may have different id numbers but are connected by their tmpID, which is, then, linked to the "id" in table 1.

 

So basically I would like to combine using the commonality that exists in the values of id and tmpid but didn't know if I could do that with two different column names?

 

Thanks in advance

[PFMaBiSmAd]

Or course you can reference the different column names. Your join would be something like the following, depending on the actual relationship and join's you want -

 

FROM table_1 t1 JOIN table_2 t2 ON (t1.id = t2.tmpid) JOIN table_3 t3 ON (t1.id = t3.tmpid)

[jerryisgood]

Or course you can reference the different column names. Your join would be something like the following, depending on the actual relationship and join's you want -

 

FROM table_1 t1 JOIN table_2 t2 ON (t1.id = t2.tmpid) JOIN table_3 t3 ON (t1.id = t3.tmpid)

 

Thank you for the quick response. I wasn't sure because everywhere I've looked it appeared that the two or three tables would have to have a common column name. So it would associate the two id's together then despite the column names?

 

Would this make sense:

select *

 

from

table1

table2

table3

 

where

    table1.id = table2.tmpid

    table2.tmpid = table3.tmpid

;

 

Edit: I didn't add any join in mine.. I'm a bit confused by the representations in your example. Would you kindly swap it with my iteration?

 

Does that make sense?

 

Also, will do you forsee me running into trouble where table two or three, since they can be expanded, may have duplicate tmpid values?

 

Ie.

 

Table 1

id-------------name-----------age

001              joe                22

002              rex                65

 

 

 

Table 2

id-------------tmpid---------services offered

001              001                  service a         

002              001                  service b

003              002                  service a

 

Table 3

id-------------tmpid------------favorite number(s)

001              001                  6     

002              002                  5

003              002                  4

 

 

 

So basically tables 2 and 3 are for the fields that could have multiple responses that can't be in one cell.

Is the above code right? and what will that give me? I have the idea in my head I'm just trying to wrap my head around all of this.. This is all stemming from an inability to choose more than one source in mail merge..grr!

 

So Joe provides services a and b and his favorite number is 6 while Rex provides service a as well but his favorite numbers are 5 and 4.

 

How would this look in the merged table? 

 

 

[jerryisgood]

Or rather,

 

SELECT table1.*, table2.*, table3.*

FROM (table1 INNER JOIN table2 ON table1.id = table2.tmpid) INNER JOIN table3 ON table3.tmpid = table2.tmpid;

 

Does that look right?

 

[jerryisgood]

That works!