Restricting inner query with outer query atttribute

Question

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.

score 0 · Accepted Answer · answered Oct 14 '16 at 11:17

0

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

answered Oct 14 '16 at 11:17

Jaydip Jadhav

12,179
6
24
40

I think you (OP) want `> 0`. And if so you can use `exists` to possibly make it faster. – shawnt00 Oct 15 '16 at 15:16
Reading the question again it also appears you may want the reverse condition: `= 0` or `not exists`. – shawnt00 Oct 15 '16 at 15:18

score 0 · Answer 2 · answered Oct 14 '16 at 15:12

SELECT * 
FROM  TRANSACTION_TABLE T 
      INNER JOIN VALUATION_TABLE V1 ON T.VAL_FK = V.ID 
WHERE V.ID IN ( SELECT ID 
                FROM   VALUATION_TABLE
                WHERE  DATE > <GIVEN DATE>
              )

If execution time is important, you may want to test the various solutions on your actual data and see which works best in your situation.

Restricting inner query with outer query atttribute

2 Answers2

Linked