I am facing a challenge in SQL. I have the following query:
x
SELECT *
FROM Books
WHERE
Categories IN ('Fiction', 'art') OR
Language IN ('en', 'de') OR
Country = 'DE'
I need to look at the result if they are at least equal to two of these factors, it will be accepted, rather will not be selected.
E.g. if a book has Category = Fiction
and Language = FR
and Country = DE
=> pass
A book with Category = History
and Language = FR
and Country = DE
=> failed
How can I achieve that in SQL?
I have tried to calculate a weight for each row. Each factor will add 0.3 to the complete weight and will pick only the one who has > 0.6
I also tried to group by. But I cannot really find the correct syntax.
Any ideas? Thanks!
P.S. I cannot put all the possibilities in my where
clause because I have more than 3 factors.
Advertisement
Answer
select * from
(
SELECT *,
case when Categories in ('Fiction', 'art') then 1 else 0 end as cat_condition,
case when Language in ('en', 'de') then 1 else 0 end as lang_condition,
case when Country = 'DE' then 1 else 0 end as country_condition
FROM Books
WHERE
Categories in ('Fiction', 'art') OR
Language in ('en', 'de') OR
Country = 'DE'
) tmp
where cat_condition + lang_condition + country_condition >= 2