MYSQL: two results in one row

Question

Iam solving an issue. Iam having 2 tables: ORDERS - which defines customer order

+-------------------+-------------+------+-----+-------------------+----------------+
| Field             | Type        | Null | Key | Default           | Extra          |
+-------------------+-------------+------+-----+-------------------+----------------+
| id                | int(11)     | NO   | PRI | NULL              | auto_increment |
| customer_id       | int(11)     | NO   | MUL | NULL              |                |
| from_date         | date        | YES  |     | NULL              |                |
| datum_dodani      | date        | NO   |     | NULL              |                |
| popis             | text        | NO   |     | NULL              |                |
| status            | tinyint(4)  | NO   |     | 1                 |                |
| cena              | varchar(8)  | NO   |     | NULL              |                |
| vytvoril          | tinyint(4)  | NO   | MUL | NULL              |                |
| vytvoreno         | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| vyresil           | tinyint(4)  | YES  |     | NULL              |                |
| prirazena_skupina | tinyint(4)  | YES  |     | NULL              |                |
| narocnost         | smallint(6) | YES  |     | NULL              |                |
| cas               | varchar(6)  | YES  |     | NULL              |                |
| priorita          | tinyint(1)  | NO   |     | 0                 |                |
+-------------------+-------------+------+-----+-------------------+----------------+

and POLOZKY_OBJEDNAVKY which defines each row of customer order:

+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| order_id  | int(11)    | YES  | MUL | NULL    |                |
| servis_id | tinyint(4) | YES  | MUL | NULL    |                |
| cena      | int(11)    | YES  |     | NULL    |                |
| pocet     | tinyint(4) | YES  |     | NULL    |                |
| popis     | text       | YES  |     | NULL    |                |
| bike      | text       | YES  |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+

These two tables are linked via polozky_objednavky.order_id -> orders.id

And the problem is that Iam not able to create query which would return something like this:

+-------------------+-------------+--------------+------------------------+
| orders.id         | customer_id | orders.status| polozky_objednavky.bike|
+-------------------+-------------+--------------+------------------------+
| 1                 | 23          |             2|              24, 25, 46|
| 2                 | 25          |             5|                      36|

So that means that in column polozky_objednavky.bike there will be select which returns 3 rows from POLOZKY_OBJEDNAVKY with key POLOZKY_OBJEDNAVKY.ORDER_ID = ORDERS.ID

Hope i explained it well and sorry for dumb question, but I tried to select it with JOINS or select from select but has not succeed :(

Thank you, M.

Answer 1

I think you need an aggregation query with group_concat():

SELECT o.id AS orders_id, o.customer_id, o.status,
       GROUP_CONCAT(p.bike SEPARATOR ', ') as bikes
FROM ORDERS o JOIN
     POLOZKY_OBJEDNAVKY po
     ON p.order_id = o.id
GROUP BY o.id, po.customer_id;

Answer 2

You need to join the tables. Try this:

SELECT o.id AS orders_id,
       o.customer_id,
       o.status,
       p.bike
  FROM ORDERS AS o
  LEFT OUTER JOIN POLOZKY_OBJEDNAVKY AS p
    ON p.order_id = o.id

If you have a specific condition just add it to the end as a WHERE clause.

You can read about joins at w3school and sitepoint