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?
3 Answers
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);
1 Comment
Nader
I couldn't vote due to my little reputation, thanks!
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
3 Comments
GeorgiG
After I saw the CTE answer, I agree it is better. Nonetheless, Join is not the same thing, because data in the columns can be used in select part of the query. Also in the first example there is no repeating code.
Nader
Do you mean? SELECT * FROM T_profile AS t1 JOIN T_Profile AS t2 ON t1.profile_id = t2.profile_id OR t1.active_Profile_id = t2.profile_id; This does not work.
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.
joinperhaps?