I am creating a query between multiple many to many relationship tables in order to chain their values together. My issue is that I can’t seem to figure a way to Group By the ‘service’ values from the ‘services’ sub-table in this query:
SELECT GROUP_CONCAT(containers.container_id ORDER BY containers.container_id DESC) AS container_id, GROUP_CONCAT(containers.container_serial ORDER BY containers.container_id DESC) AS container_serial, GROUP_CONCAT(containers.container_index ORDER BY containers.container_id DESC) AS container_index, invoices.*, statuses.status, clients.client, GROUP_CONCAT(services.service_id ORDER BY containers.container_id DESC) AS service_id, GROUP_CONCAT(services.service ORDER BY containers.container_id DESC) AS service FROM containers_invoices LEFT JOIN containers ON containers_invoices.container_id = containers.container_id LEFT JOIN invoices ON containers_invoices.invoice_id = invoices.invoice_id LEFT JOIN statuses ON invoices.status_id = statuses.status_id LEFT JOIN clients ON invoices.client_id = clients.client_id LEFT JOIN invoices_services ON invoices.invoice_id = invoices_services.invoice_id LEFT JOIN services ON invoices_services.service_id = services.service_id GROUP BY containers_invoices.invoice_id ORDER BY invoice_id DESC
Right now my result looks something like this:
- C09-65475u76u76, C08-dfsgreg345g3 – 114 – ? – Service,Service – 1000 – Sent
- C09-65475u76u76, C08-dfsgreg345g3, C03-dfsdfsd, C02-sadasd – 117 – ? – Line,Line,Line,Line – 3000 – Paid
The service should show up only once and not loop as many times as the containers number that I have. I know I am close, but I can’t seem to close that solution gap. Thanks for your help!
You are grouping by invoice:
GROUP BY containers_invoices.invoice_id
You should group by service from what I understand
GROUP BY services.service_id
EDIT: I guess I misunderstood the question. You’d like to keep one row per invoice but in your group_concat not list mutliple times the same value.
If it is so, you can use the DISTINCT keyword:
GROUP_CONCAT(DISTINCT services.service ORDER BY containers.container_id DESC) AS service