CODEWITHSUNDEEP
mysqlsqlmariadb
One Normal Night
One Normal Night

Reputation: 332

Merge data from two cells from the same column in different rows in MySQL/MariaDB

I am having trouble searching for an answer to this question, because of my lack of knowledge about the terminology and SQL, even though I know it probably exists.

I have a database with the following tables:
desc pkm;

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| pkm_code        | int(11)       | NO   | PRI | NULL    |       |
| pkm_name        | varchar(32)   | NO   | UNI | NULL    |       |
| pkm_category    | varchar(32)   | NO   |     | NULL    |       |
| pkm_description | varchar(1280) | NO   |     | NULL    |       |
| pkm_weight      | float         | NO   |     | NULL    |       |
| evolution_code  | int(11)       | YES  | MUL | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+

desc poketype;

+---------------------+------------+------+-----+---------+-------+
| Field               | Type       | Null | Key | Default | Extra |
+---------------------+------------+------+-----+---------+-------+
| pkm_code            | int(11)    | NO   | PRI | NULL    |       |
| type_code           | int(11)    | NO   | PRI | NULL    |       |
| poketype_is_primary | tinyint(1) | NO   |     | NULL    |       |
+---------------------+------------+------+-----+---------+-------+

desc type;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| type_code | int(11)     | NO   | PRI | NULL    |       |
| type_name | varchar(32) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

And so far I have the following SQL command:

SELECT pkm.pkm_code, pkm.pkm_name,type.type_name FROM poketype
    JOIN pkm ON pkm.pkm_code=poketype.pkm_code
    JOIN type ON poketype.type_code=type.type_code
    WHERE pkm.pkm_code<=151
    ORDER BY pkm_code;

Which displays the primary and secondary types on separate lines.
How would I get both types to display on the same row for dual-type pokemon?

My current results:

+-----------+-------------+-----------+
| pkm_code  | pkm_name    | type_name |
+-----------+-------------+-----------+
| 1         | Bulbasaur   | grass     |
| 1         | Bulbasaur   | poison    |

Desired results:

+-----------+-------------+-------------+
| pkm_code  | pkm_name    | type_name   |
+-----------+-------------+-------------+
| 1         | Bulbasaur   | grass,poison|

(Yes, bulbasaur is a dual type. I was surprised too!)

Upvotes: 1

Views: 179

Answers (1)

Shadow
Shadow

Reputation: 34231

Use mysql's group_concat() function to combine values from different records in a single value:

SELECT pkm.pkm_code, pkm.pkm_name, group_concat(type.type_name) as typename FROM poketype
    JOIN pkm ON pkm.pkm_code=poketype.pkm_code
    JOIN type ON poketype.type_code=type.type_code
    WHERE pkm.pkm_code<=151
    GROUP BY pkm.pkm_code, pkm.pkm_name;

Upvotes: 2

Related Questions