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?
Thank.
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_lines.id = 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.