Mysql, inner join in multiple columns with the same name

Question

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.

You must be using an out-of-date version of google. This is SQL 101. Each instance of catalog requires its own alias. — Strawberry
– Strawberry, Commented Oct 24, 2013 at 15:26
But what would happen if a col in data has an id not present in catalog? — Mosty Mostacho
– Mosty Mostacho, Commented Oct 24, 2013 at 15:30

Sjerdo · Accepted Answer · 2013-10-24 15:30:03Z

4

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

answered Oct 24, 2013 at 15:30

Sjerdo

1871 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql, inner join in multiple columns with the same name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related