Hi all,
I'm making a small time tracking web page for my own use.
I'm having lots of fun doing it.
Let's say I have a table to store the tasks (programming at the office, programming at the customer place...) which has the following fields:
idTask, idProject, idUser, idTaskType, idTaskUse, idInvoice, date, startTime, endTime, notes.
And another table to store the expenses (gasoline, hotel, lunch, ...) which has the following fields:
idExpense, idProject, idUser, idExpenseType, idInvoice, date, notes.
Now, I plan to prepare the invoice and as both tables have the idInvoice... I've already prepared the assignment of this id into both tables.
So I now have rows in both (tasks and expenses) that have the right invoice ID.
In order to show the contents of the invoice, now I have to select the fields of both tables and order them by date. I don't want to have table 1 and afterwards table 2 data...
In my invoice I would like to show:
description => text monunted using the task name or the expense name.
units => amount of hours if we talk about tasks or amount of km (for example) if we speak about expenses.
Unit cost => cost per unit of that task or expense.
subtotal => calculated cost units * unit cost.
How should I do it?
My head is moving me to a VIEW, also to FULL JOIN, or even making both queries and ordering it manually...
What I have tried:
Nothing, still can't imagine which direction to take.