1

DB noob here.

I have a table named invoice with a m:m relationship with a few other tables via junction tables:

  • invoice_customer
  • invoice_creator
  • invoice_deposit
  • invoice_withdrawal
  • invoice_payment_method

I'm trying to execute a query that will LEFT JOIN all of the data of these m:m relationships, however, seemingly at random, I'm getting duplicate results. Sometimes it gives the right amount, then when some state is modified it will give duplicates, and I can't seem to figure out why.

For example, this query gives me a result of "1" given I have one record matching the query:

SELECT COUNT(*)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL

But this query gives me 4 duplicate records instead of 1:

SELECT invoice.*, account.*, customer.*, payment_method.*, deposit.*, withdrawal.*
FROM invoice
LEFT JOIN account_invoice
    ON account_invoice.invoice_id = invoice.id
LEFT JOIN account
    ON account.id = account_invoice.account_id
LEFT JOIN invoice_customer
    ON invoice_customer.invoice_id = invoice.id
LEFT JOIN customer
    ON customer.id = invoice_customer.customer_id
LEFT JOIN invoice_payment_method
    ON invoice_payment_method.invoice_id = invoice.id
LEFT JOIN payment_method
    ON payment_method.id = invoice_payment_method.payment_method_id
LEFT JOIN invoice_deposit
    ON invoice.id = invoice_deposit.invoice_id
LEFT JOIN transaction AS deposit
    ON deposit.id = invoice_deposit.transaction_id
LEFT JOIN invoice_withdrawal
    ON invoice.id = invoice_withdrawal.invoice_id
LEFT JOIN transaction AS withdrawal
    ON withdrawal.id = invoice_withdrawal.transaction_id
WHERE account.id = ?
    AND invoice.deleted_at IS NULL
ORDER BY invoice.id DESC
LIMIT ?,?
  • 1
    They are not really duplicates. If you have an invoice related with 2 deposits and 2 withdrawals, your query will rightfully return 4 rows. That's what you asked for. – ypercubeᵀᴹ Dec 22 '18 at 10:29
  • 1
    If there are also 3 related payment methods the result will be 12 rows. One for each combination of related deposits, withdrawals and payment methods. – ypercubeᵀᴹ Dec 22 '18 at 10:31
  • It seems like payment_method would be tied to deposit and to withdrawal?? – Rick James Dec 22 '18 at 16:27
  • @RickJames Yes it's used for the withdrawal mostly, but I just attach it to the invoice for now. – Lansana Dec 22 '18 at 16:31
  • @yper-crazyhat-cubeᵀᴹ Good point, however there is only one result in each of the junction tables. – Lansana Dec 22 '18 at 16:32
0

If you want only one row of output for each Invoice, you need to figure out what to do with multiple customers tied to that one invoice. (Etc.) Here's one approach:

SELECT i.*,
       GROUP_CONCAT( 
           ( SELECT c.name FROM invoice_customers AS ic
                           JOIN customers AS c  USING(customer_id)
                           WHERE ic.invoice_id = i.invoice_id
           ) SEPARATOR ", "
                   ) AS customers
    FROM invoices AS i
    ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.