CODEWITHSUNDEEP
mysqljoin
Dar
Dar

Reputation: 401

MySQL Results a list separated by comma and values from different table

I have two tables, I want to join values separated by comma and show them one field show all values that contain the id from the other table.

Example:

This is table1

--------------------------------------
|   id  |   title   |   area         |
| -----------------------------------|
|   1   |   hi      |   1,12,14      |
| -----------------------------------|
|   2   |   by      |   12,14,2      |
| -----------------------------------|
|   3   |   me      |   14,2         |
--------------------------------------

This is table2

---------------------
|   id  |   name    |
|-------------------|
|   1   |   Wa      |
|-------------------|
|   12  |   La      |
|-------------------|
|   14  |   Sa      |
|-------------------|
|   2   |   Tn      |
---------------------

And I would like to display this 

------------------------------------
|   id  |   title   |   area       |
|----------------------------------|
|   1   |   hi      |   Wa,La,Sa   |
|----------------------------------|
|   2   |   by      |   La,Sa,Tn   |
|----------------------------------|
|   3   |   me      |   Sa,Tn      |
------------------------------------

Simply replacing values from table1 by the names from table2 by id.

Upvotes: 0

Views: 386

Answers (1)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Ideally you should create a new table named for example table1_has_table2

CREATE TABLE table1_has_table2 (
    table1_id INT(11) UNSIGNED NOT NULL,
    table2_id INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (table1_id,table2_id)
)

This then allows your n:m relationship to be performed very simply i.e.

SELECT 
    table1.id,
    table1.title,
    GROUP_CONCAT(table2.name) AS area

FROM table1

INNER JOIN table1_has_table2
ON table1_has_table2.table1_id = table1.id

INNER JOIN table2
ON table2.id = table1_has_table2.table2_id

GROUP BY table1.id

This method is also far more efficient in the long run

Upvotes: 4

Related Questions