I currently have a large SQL query (not mine) which I need to modify. I have a transaction and valuation table. The transaction has a one-to-many relationship with valuations. The two tables are being joined via a foreign key.
I've been asked to prevent any transactions (along with their subsequent valuations) from being returned if no valuations for a transaction exist past a certain date. The way I thought I would achieve this would be to use an inner query, but I need to make the inner query aware of the outer query and the transaction. So something like:
SELECT * FROM TRANSACTION_TABLE T
INNER JOIN VALUATION_TABLE V WHERE T.VAL_FK = V.ID
WHERE (SELECT COUNT(*) FROM V WHERE V.DATE > <GIVEN DATE>) > 1
You may looking for this
SELECT * FROM TRANSACTION_TABLE T INNER JOIN VALUATION_TABLE V1 ON T.VAL_FK = V1.ID WHERE (SELECT COUNT(*) FROM VALUATION_TABLE V2 WHERE V2.ID = V1.ID AND V2.DATE > <GIVEN DATE>) > 1