How I can select data from three tables to three column?

Question

I have the next database tables:

How I can select data to this format:

I'm trying use this sql:

SELECT b.id, b.name, name_translate
FROM books b
LEFT JOIN books_translates bt
ON bt.book_id = b.id
LEFT JOIN translates t
ON t.id = bt.translate_id

But I get only:

Two columns with the same name doesn't make any sense. Maybe you should use `GROUP_CONCAT` to get all the names in one cell. — Barmar, Oct 23 '15 at 18:38
Possible duplicate of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — Djizeus, Oct 23 '15 at 18:40
Would you please provide sample data? Some rows for your three tables with data to test this... — Shnugo, Oct 23 '15 at 18:46

score 1 · Answer 1 · answered Oct 23 '15 at 18:50

1

You're trying to do something called a pivot. This is a notorious pain in the neck in MySQL.

You might try this:

SELECT b.id, b.name, 
       GROUP_CONCAT(t.name_translate ORDER BY t.name_translate) translations
  FROM books b
  LEFT JOIN books_translates bt ON bt.book_id = b.id
  LEFT JOIN translates t ON t.id = bt.translate_id
 GROUP BY b.name, b.id
 ORDER BY b.name, b.id

It will pull all your translations into a single column.

answered Oct 23 '15 at 18:50

O. Jones

103,626
17
118
172

Hi there Ollie, maybe you want check my answer. – Juan Carlos Oropeza Oct 23 '15 at 20:59

Juan Carlos Oropeza · Answer 2 · 2015-10-23T22:57:12.927

I will create a temporal table to make it easy to read. But you can use a derivated table.

SqlFiddleDemo

lenguaje will be the column header, and I use the last 3 characters to get that lenguaje.

CREATE TABLE result as 
  SELECT b.id, b.name, SUBSTRING(name_translate, -3 ) lenguaje, name_translate
  FROM books b
  LEFT JOIN books_translates bt
  ON bt.book_id = b.id
  LEFT JOIN translates t
  ON t.id = bt.translate_id;

Then you need create the query dynamically

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(lenguaje = ''',
      lenguaje,
      ''', name_translate, NULL)) AS ',
      lenguaje
    )
  ) INTO @sql
FROM result
;

SET @sql = CONCAT('SELECT id, name, ', @sql, ' FROM result GROUP BY id'); 
                                                  ^^^^^^^^^^
                                                 (..query..)  <- derivated table instead
PREPARE stmt FROM @sql;
EXECUTE stmt;

OUTPUT

| id |  name |      ENG |      LAT |
|----|-------|----------|----------|
|  1 | Mario | MarioENG | MarioLAT |
|  2 | Artur | ArturENG |   (null) |

How I can select data from three tables to three column?

2 Answers2