darkpool darkpool - 2 months ago 4x
SQL Question

Ensuring two columns only contain valid results from same subquery

I have the following table:

id symbol_01 symbol_02
1 abc xyz
2 kjh okd
3 que qid

I need a query that ensures symbol_01 and symbol_02 are both contained in a list of valid symbols. In other words I would needs something like this:

select *
from mytable
where symbol_01 in (
select valid_symbols
from somewhere)
and symbol_02 in (
select valid_symbols
from somewhere)

The above example would work correctly, but the subquery used to determine the list of valid symbols is identical both times and is quite large. It would be very innefficient to run it twice like in the example.

Is there a way to do this without duplicating two identical sub queries?


You could try use a CTE like;

WITH ValidSymbols AS (
    SELECT DISTINCT valid_symbol
    FROM somewhere
SELECT mt.* 
FROM MyTable mt
INNER JOIN ValidSymbols v1
    ON mt.symbol_01 = v1.valid_symbol
INNER JOIN ValidSymbols v2
    ON mt.symbol_02 = v2.valid_symbol