MySQL - Join two tables, different columns in table 1 on the same column in table 2

Question

I'm trying to join a different column (part_type_n (where n ranges from 1 to 54)) on Table1 with the same column (id, primary, autoinc) on Table2.

Schema:

Table1
==============
part_type_1
.
.
.
part_type_54



Table2
=============
id

I tried the obvious query (php generated, looping through n from 1 to 54), omitted repetitive stuff in ...:

SELECT * FROM Table1 JOIN Table2 on (Table1.part_type_1=Table2.id), ..., (Table1.part_type_54=Table2.id)

I receive this error:

1066 - Not unique table/alias: 'Table2'

How do I join these two tables?

In this case, would it be better to just use 54 separate calls instead of a single call? — ina, Oct 13 '13 at 01:41
Do you really want to select only those records from table1 which have table2 records matching all 54 part_type values? — , Oct 13 '13 at 09:06

Sajuna Fernando · Answer 1 · 2013-10-13T08:56:08.230

0

You will have to join the table on it self again multiple times.

SELECT * FROM table1 t1 
INNER JOIN table2 t2 on t2.Id=t1.part_type_1 
INNER JOIN table2 t3 on t3.id = t1.part_type_54;

Hope this helps!

edited Oct 13 '13 at 08:56

answered Oct 13 '13 at 00:46

Sajuna Fernando

1,314
9
15

why inner join and not join – ina Oct 13 '13 at 01:03
http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins?sort=newest%3E – Sajuna Fernando Oct 13 '13 at 01:09
JOIN is shorthand for INNER JOIN - is there a reason to not use shorthand – ina Oct 13 '13 at 01:40
There is absolutely no difference in performance as well as implementation. I just personally prefer to write Inner Join as its much cleaner to read. – Sajuna Fernando Oct 13 '13 at 08:54

score 0 · Answer 2 · answered Oct 13 '13 at 09:02

As an alternative to writing a query with 54 table aliases, you could consider joining to the table once - like so:

select ...
from Table1 t1
join Table2 t2
  on t2.id in (t1.part_type_1, t1.part_type_2, ... t1.part_type_54)

Govindaraj · Answer 3 · 2014-08-30T11:13:26.877

It worked for me to get my required result as one row of which matches various categories all stored in one table column.

Query

       SELECT cm3.*, xp.post_title,GROUP_CONCAT(DISTINCT sc.name) AS cate_list
       FROM `xld_posts` xp 
       JOIN course_map cm0 ON cm0.course_id = xp.ID
       JOIN course_map cm1 ON cm1.course_id = cm0.course_id  AND cm0.id = 3
       JOIN course_map cm2 ON cm2.course_id = cm1.course_id  AND cm1.id = 6
       JOIN course_map cm3 ON cm3.course_id = cm2.course_id  AND cm2.id = 11
       JOIN subject_category sc ON cm3.id = sc.id
       GROUP by post_title ORDER BY post_title

Note: the categories values 3, 6, and 7 are got from form sumbit. Thus if your form has more than three or less your query should dynamically created and join each table with previous table.

:) Happy if any one felt useful.

MySQL - Join two tables, different columns in table 1 on the same column in table 2

1066 - Not unique table/alias: 'Table2'

3 Answers3