DB noob here.
I have a table named
invoice with a m:m relationship with a few other tables via junction tables:
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 ?,?