jerryisgood Posted August 13, 2010 Share Posted August 13, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/210597-joining-three-tables-with-different-column-name/ Share on other sites More sharing options...
PFMaBiSmAd Posted August 13, 2010 Share Posted August 13, 2010 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) Quote Link to comment https://forums.phpfreaks.com/topic/210597-joining-three-tables-with-different-column-name/#findComment-1098678 Share on other sites More sharing options...
jerryisgood Posted August 13, 2010 Author Share Posted August 13, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/210597-joining-three-tables-with-different-column-name/#findComment-1098696 Share on other sites More sharing options...
jerryisgood Posted August 13, 2010 Author Share Posted August 13, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/210597-joining-three-tables-with-different-column-name/#findComment-1098700 Share on other sites More sharing options...
jerryisgood Posted August 13, 2010 Author Share Posted August 13, 2010 That works! Quote Link to comment https://forums.phpfreaks.com/topic/210597-joining-three-tables-with-different-column-name/#findComment-1098767 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.