Robert Meyer Robert Meyer - 1 month ago 12
SQL Question

Restricting inner query with outer query atttribute

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


Obviously the above wouldn't work as the inner query is separate and I can't reference the outer query V reference from the inner. How would I go about doing this, or is there a simpler way?

This would just be the case of setting the WHERE V.DATE > in the outer query as I want to prevent any valuation for a given transaction if ANY of them exceed a specified date, not just the ones that do.

Many thanks for any help you can offer.

Answer

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
Comments