Is a separate query good enough or is there some kind of join I can make?

Basically I have a small "invoicing" type system in which I have a MySQL table set up for individual invoices, invoice rows and items associated with each invoice, like:

tabular accounts:

fields: invoice_id | type | fees | comments | company



invoice_lines table:

fields: id | invoice_id | name | description | quantity



invoice_affected_items table

fields: id | invoice_id | item_id


I can have many account lines and many affected items per account ID. In this case, the best would be a join, or should I just join the account rows to the table and retrieve the affected items with a separate query?



source to share

3 answers

You can join, but this will expose a lot of extra data. If you attach lines to invoices, for example, you will be pulling the invoice multiple times - once for each line ...

So to answer your question. I would do this in separate queries.



IMHO If the affected items have a one-to-one relationship with the invoice lines (via = invoice_affected_items.item_id) you can do one of two things

Or keep the current design and make separate requests

or redesign as follows

  • Delete tables completely invoice_affected_items

  • Add flag invoice_affected_items

    to tableinvoice_lines

  • Index invoice_lines

    by (invoice_affected_items, invoice_id) to see which invoices affected the items
  • Index invoice_lines

    by (invoice_affected_items, id) to see which invoice rows are affected items.


If there is no relationship between invoice_lines and invoice_affected_items, I would make 2 separate requests.



All Articles