sql - Two conditions based on subquery. How to shorten it?

Question

I have working query basing on 2 subqueries.

SELECT * FROM database1
where account_id in (SELECT account_id FROM (select account_id,transaction_id from ... here i have my big sql query))
and transaction_id in (SELECT transaction_id FROM (select account_id,transaction_id from ... here i have my big sql query))

I would like to know what to do to do not have to paste 2 times my big sql query and just use this two conditions in one line? Is this possible?

Answer 1

I am guessing that you really want:

select d.*
from database1 d
where exists (select 1
              from <big query here> q
              where q.account_id = d.account_id and
                    q.transaction_id = d.transaction_id
             );

Unlike your query, this guarantees that the two columns are on the same row in your query. That is not what your query does, but I'm guessing it is your intention.

If you really want to check if either occurs anywhere, then you can use a CTE:

with q as (
      <your query here>
     )
select d.*
from database1 d
where exists (select 1 from q where q.account_id = d.account_id) and
      exists (select 1 from q where q.transaction_id = d.transaction_id);