List an item accompaniments from a mysql sale

Question

Table 1 - sale
id_venda

Table 2 - item_request
id | id_venda | id_pastel

Table 3 - item_accompaniment
id | id_item_request | id_follow-up

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

A pastel:
meat (item requested)
Accompaniments(item_accompaniment)
onion and parsley.

A pastel:
chicken(item_request)
Accompaniment:
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

SELECT 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

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

Answer 1

Utilizes the distinct in the SELECT what you do. I think it presents the set or the last record inserted with the id_sale.

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 doesn’t work, the idea is to create an auxiliary table that combines common sales.