List an item's accompaniments from a sale with mysql

Question

List an item's accompaniments from a sale with mysql

Navigation

#1 by (2 votes)

2

Table 1 - sales
sales_id

Table 2 - order_order id | sales_id | id_pastel

Table 3 - Tracking item
id | id_item_pedido | id_accounting

Example: I make a sale with two items and their accompaniments:

A cake:
meat (item_edited)
Side dishes (item_accompaniment)
onion and parsley.

A cake:
chicken (item_edited)
Follow-up:
catupiry (item_accompaniment).

Separately I can even bring:

SELECT venda.id_venda, 
       GROUP_CONCAT(item_pedido.id_pastel) AS id_pastel 
FROM venda 
JOIN item_pedido ON item_pedido.id_venda = venda.id_venda GROUP BY item_pedido.id_venda

SELECTitem_pedido.id_venda,item_pedido.id_pastel,GROUP_CONCAT(item_acompanhamento.id_acompanhamento)ASid_acompanhamentoFROMitem_pedidoJOINitem_acompanhamentoONitem_pedido.id=item_acompanhamento.id_item_pedidoGROUPBYitem_pedido.id

But I would like to bring only one sale with the two items with their respective accompaniments.

mysql

asked by anonymous 11.04.2018 / 01:35

1 answer

Change the color of a background image on Android How to 'reverse' Google Charts chart?

score 2 · Answer 1

Uses distinct in SELECT you do. I think it displays the whole or the last record inserted with the sales_id.

SELECT DISTINCT item_pedido.id_venda, item_pedido.id_pastel,
GROUP_CONCAT(item_acompanhamento.id_acompanhamento) AS id_acompanhamento 
FROM item_pedido 
JOIN item_acompanhamento ON item_pedido.id = item_acompanhamento.id_item_pedido 
GROUP BY item_pedido.id

If it does not work, the idea is to create an auxiliary table that joins common sales.