Select data from two tables with different fields

See more:

MySQL

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.