I search a solution for the following problem:
There are three tables: [MANDATOR], [PRODUCT] and [CONTRACT]
I want to get all current contracts for every Product for every mandator.
If I make a INNER JOIN over PRODUCT, CONTRACT and MANDATOR, I will
get all contracts and not only the current contract for the relation mandator / product.
I tried to find a solution with ContarctID IN (SELECT TOP 1 ... WHERE ValidFrom <= getdate() ORDER BY ValidFrom DESC) but then I will get only
one contract for on product an not for every mandator / product relation.
So I need something like this:
SELECT * FROM MANDATOR
SELECT * FROM PRODUCT p
INNER JOIN CONTRACT c on c.ProductID = p.ProductID
AND c.ContractID IN
SELECT TOP 1 ContractID FROM CONTRACT WHERE ValidFrom <= getdate()
AND ProductID = p.ProductID ORDER BY ValidFrom DESC
WHERE MandatorID = [*ID]
Thanks for help.