CODEWITHSUNDEEP
mysqlinner-join
Klimid1689
Klimid1689

Reputation: 111

Mysql, inner join in multiple columns with the same name

So, I have two tables, one of which is like this:

Table: data

  id  |   col_1   |   col_2   |   col_3   |   col_4
------------------------------------------------------
  1   |     167   |    12     |     15    | something1
  2   |     198   |    27     |     12    | something2
  3   |     253   |    15     |     17    | something3

and Table: catalog

  id  |   col_1
-----------------
  12  |   red
  15  |   yellow
  17  |   pink
  27  |   green

Now, what I am trying to do is get col_1, col_2, col_3 and col_4 from the data table, but instead of getting the values of col_2 and col_3, these should be replaced by the col_1 of the catalog table, according to the catalog id.

For example, I want it to return, for every row of the data table, the following info:

    167 | red    | yellow | something1
    198 | green  | red    | something2
    253 | yellow | pink   | something3

I tried this:

SELECT data.col_1, catalog.col_1, catalog.col_1, data.col_4
FROM data
INNER JOIN catalog ON data.col_2 = catalog.id
INNER JOIN catalog ON data.col_3 = catalog.id

but to no effect and surprise.

I simply can't find how to inner join multiple columns of one table to a single column of another, and Google has not been of great help. I actually don't know if I am searching with the right keywords.

Upvotes: 3

Views: 7313

Answers (1)

Sjerdo
Sjerdo

Reputation: 187

You have to give the tablenames an alias, because it doesn't know where catalog refers to.

SELECT data.col_1, c1.col_1, c2.col_1, data.col_4
FROM data
INNER JOIN catalog c1 ON data.col_2 = c1.id
INNER JOIN catalog c2 ON data.col_3 = c2.id

Upvotes: 4

Related Questions