Join Table to get name of column with two id's

Question

I have a bad designed database and need to join two tables correctly. I need a query with all fields from Table1 and replace books_ordered with the first title in table2 with the same id. The first title of each book id is in german and i need only the german title.

EDIT: I updated the tables to be more specific

This is an example table.

Table1

|___id___|__name__|_books_ordered_|
|    1   | peter  |     24,25,26  |
|    2   | olaf   |     25,26     |
|    3   | walter |     24        |
|    4   | tim    |     24,25     |

Table2

|___id___|____title____|___book_id___|
|    1   | Das Haus    |     24      |
|    4   | The house   |     24      |
|    7   | la maison   |     24      |
|    10  | la casa     |     24      |
|    2   | Der Baum    |     25      |
|    5   | The tree    |     25      |
|    8   | L'arbre     |     25      |
|    11  | El árbol    |     25      |
|    3   | Das Boot    |     26      |
|    6   | The boat    |     26      |
|    9   | Le bateau   |     26      |
|    12  | El barco    |     26      |

What i have so far is this query:

SELECT t.name, tr.title, FROM Table1 AS t 
INNER JOIN Table2 AS tr 
ON t.books_ordered = tr.book_id 
WHERE tr.id<3

But the result is just giving me the title of the first id in books_ordered back. Kinda like this:

|___name____|___title_____|
|   Peter   |   Das Haus  |
|   Peter   |   Das Haus  |
|   Peter   |   Das Haus  |
|   Olaf    |   Der Baum  |
|   Olaf    |   Der Baum  |
|   Walter  |   Das Haus  |
|   Tim     |   Das Haus  |
|   Tim     |   Das Haus  |

The result what i need should look like this:

|___name____|___title__________________________|
|   Peter   |   Das Haus, Der Baum, Das Boot   |
|   Olaf    |   Der Baum, Das Boot             |
|   Walter  |   Das Haus                       |
|   Tim     |   Das Haus, Der Baum             |

and is not cleat how you get seprated rows using the book_ordered comma separated values .. — ScaisEdge, Sep 19 '17 at 09:08
looking to you updated question .. i think you can't do this using sql only ..but you need to process the book_ordered server side for obtain the related title — ScaisEdge, Sep 19 '17 at 09:12
The marked duplicate question solved my problem, thank you anyways — kratze, Sep 19 '17 at 09:35

score 0 · Answer 1 · answered Sep 19 '17 at 09:38

0

Try this

SELECT t.name, GROUP_CONCAT(tr.title SEPARATOR ', '), FROM Table1 AS t 
INNER JOIN Table2 AS tr 
ON t.books_ordered = tr.book_id 
WHERE tr.id<3
GROUP BY t.name

answered Sep 19 '17 at 09:38

krishnar

2,537
9
23

This throws an error that the syntax is wrong but i solved it already. Thank you anyways :) – kratze Sep 19 '17 at 09:43

score 0 · Answer 2 · answered Sep 19 '17 at 09:49

0

I solved it with the linked duplicate question. The answer for this specific problem is:

SELECT distinct t.name, tr.title 
    FROM Table1 AS t 
    INNER JOIN Table2 AS tr 
    ON find_in_set(tr.book_id, t.books_ordered)

answered Sep 19 '17 at 09:49

kratze

186
2
11

Join Table to get name of column with two id's

2 Answers2