Php MySQL Many to Many to Many condition Group By


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:

    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, 
    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:

  1. C09-65475u76u76, C08-dfsgreg345g3 – 114 – ? – Service,Service – 1000 – Sent
  2. 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!

>Solution :

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 

Leave a Reply Cancel reply