How to combine two row in sql?

Question

Below are the details of my table.

show tables;
+-------------------+
| Tables_in_db      |
+-------------------+
| bus_type          |
| op_bus            |
| op_names          |
+-------------------+
3 rows in set (0.01 sec)

mysql> select * from bus_type;
+-------------+----------------+
| bus_type_id | bus_type_name  |
+-------------+----------------+
|         101 | AC             |
|         102 | Non AC         |
|         103 | Non AC Sleeper |
|         104 | AC Sleeper     |
|         105 | MINI Bus       |
|         106 | TT             |
+-------------+----------------+
6 rows in set (0.00 sec)

mysql> select * from op_bus;
+-----------+-------+-------------+
| op_bus_id | op_id | bus_type_id |
+-----------+-------+-------------+
|         1 |     1 |         101 |
|         2 |     1 |         102 |
|         3 |     2 |         104 |
|         4 |     2 |         103 |
+-----------+-------+-------------+
4 rows in set (0.00 sec)

mysql> select * from op_names;
+-------+---------+
| op_id | op_name |
+-------+---------+
|     1 | VRL     |
|     2 | SRS     |
+-------+---------+
2 rows in set (0.01 sec)

I want output like below

+---------+--------------------------------+
| op_name | bus_type_name                  |
+---------+--------------------------------+
| VRL     | AC,Non AC                      |
| SRS     | AC Sleeper, Non AC Sleeper     |
+---------+--------------------------------+    

I'm trying to fetch operator_name along with the type of the bus, if a bus_operator has a different bus type then it should display it in a single row.

I tried the below query and got different output.

mysql> select op.op_name, bt.bus_type_name from op_bus as ob inner join op_names as op on ob.op_id=op.op_id inner join bus_type as bt on ob.bus_type_id=bt.bus_type_id group by op.op_name,bus_type_name;
+---------+----------------+
| op_name | bus_type_name  |
+---------+----------------+
| VRL     | AC             |
| VRL     | Non AC         |
| SRS     | AC Sleeper     |
| SRS     | Non AC Sleeper |
+---------+----------------+
4 rows in set (0.00 sec)

I also tried group by op_name it gave an error saying

select op.op_name, bt.bus_type_name from op_bus as ob inner join op_names as op on ob.op_id=op.op_id inner join bus_type as bt on ob.bus_type_id=bt.bus_type_id group by op.op_name,bus_type_name group by op.op_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by op.op_name' at line 1

Answer 1

Group concat is probably what you're looking for here. This might help: MySQL : Multiple row as comma separated single row