CODEWITHSUNDEEP
sqlpostgresql
Nader
Nader

Reputation: 96

SQL Query with common filter for multiple column

The filter in the following query are identical.

select * from t_profile where 
profile_id in        (select profile_id from t_profile ...) or 
active_profile_id in (select profile_id from t_profile ...);

Can we avoid repeating the filter?

Upvotes: 2

Views: 1476

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can avoid repeating the filter by using exists rather than in:

select p.*
from t_profile p
where exists (select 1
              from t_profile p2 . . .
              where p2.profile_id = p.profile_id or
                    p2.profile_id = active_profile_id
             );

That said, repeating the filter using either in or exists is probably the better option.

Upvotes: 0

GeorgiG
GeorgiG

Reputation: 1103

You can JOIN:

SELECT * FROM T_profile1 AS t1
JOIN T_Profile2 AS t2
ON t1.profile_id = t2.profile_id 
 OR t1.active_Profile_id = t2.profile_id

If you don't want repeating columns, you can do separate queries and UNION them

SELECT * FROM T_profile1 AS t1
JOIN T_Profile2 AS t2
ON t1.profile_id = t2.profile_id

UNION

SELECT * FROM T_profile1 AS t1
JOIN T_Profile2 AS t2
ON t1.active_Profile_id = t2.profile_id

I recommend you reading https://www.w3schools.com/sql/default.asp for more SQL fundamentals

Upvotes: 0

user330315
user330315

Reputation:

You can use a common table expression for that:

with filter as (
   select profile_id 
   from ...
   where ...
)
select *
from t_profile
where profile_id in (select profile_id from filter)
   or active_profile_id in (select profile_id from filter);

Upvotes: 2

Related Questions