Php MySQL Many to Many to Many condition Group By-CodePudding

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!

CodePudding user response：

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