Query help

[icontatt]

Hey Guys!



I`m working on a prject and i don`t know if a query can handl it like i want it to.

i got 5 tables

1.: invoice

id	subject	amount
1	Invoice 1	10€
2	Invoice 2	20€

2.: productsinvoice

id	product	invoiceid (=invoice id)
1	Desk	1
2	Table	1

3.: incominginvoice

id	subject	amount
1	Invoice 2013-1	5€
2	Invoice 2013-2	2€

4.: productsincominginvoice

id	product	invoiceid (=incoming invoice id)
1	food	1
2	food	2

5. tax informations about table 1 and 2

id	prodid	IsIncomingInvoice(bool) (false -> invocie, true ->...)	taxinformation
1	1	false	xyz
2	1	false	abc

i want to join all information, so that i see every product, the products invoice subject and the tax informations.

i can easily (left f.a.) join 2 with 1
and also
join 4 with 3

sow at least i got to join the tax informations. 1 problem is, that there can be "n" taxinformations for the same product (i can overwork my taxinformations for a product and also need the old settings to be saved in the table)

so if i do a single join on prodid, i can`t differ with my "isincomminginvoice" if the prodID ist prodID of 2 or 4.

and this is the part i don`t know how to handl it.

is it posible to first select for example all invoices of 5, and joining with this selected table?
like using a filter....

pls give me tips

mario

[June7]

Which tax information do you want for a product - the last one entered to table?

Want to provide db for analysis? Follow instructions at bottom of my post.

[icontatt]

Which tax information do you want for a product - the last one entered to table?

That are just some information about how to tax the product. Which VAT and so on.
The main Problem is, that if i go:

SELECT productsinvoice
FROM 2
LEFT JOIN 5
ON 2.id = 5.prodid;

it joins all prodid with id's. thats clear to me. but i only what to join those, where 5.IsIncomingInvoice = false

mario

[June7]

I suspect will need to do selective query before joining. Queries can be joined to tables or other queries. So do a query that returns the desired tax records then join query to table.