CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mariano G
Mariano G

Reputation: 255

Select Persons that match with all criterias

I'm trying to run a query but can't get it

Here is the structure:

Person:
  IdPerson
  FirstName
  LastName
  ...

CustomField
  IdField
  ....

CustomFieldXPerson
  IdField
  IdPerson
  Value

Filter:
  IdField
  Value
  Criteria (<, >, <=, >=, =)
  GroupId

I need to get all persons that match with all filters, and also get all persons that match at least with one... 

SELECT IdPerson
FROM Person p
  JOIN CustomFieldXPerson cfxp on cfxp.IdPerson = p.IdPerson
  JOIN Filter f on f.IdField = cfxp.IdField AND f.Value (f.Criteria) cfxp.Value
WHERE f.GroupId = X

There is an easy way to do this dynamically?

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can express your query as:

SELECT IdPerson
FROM Person p JOIN
     CustomFieldXPerson cfxp
     on cfxp.IdPerson = p.IdPerson JOIN
     Filter f
     on f.IdField = cfxp.IdField AND
        (f.Criteria = '=' and f.Value = cfxp.Value or
         f.Criteria = '<' and f.Value < cfxp.Value or
         f.Criteria = '<=' and f.Value <= cfxp.Value or
         f.Criteria = '>' and f.Value > cfxp.Value or
         f.Criteria = '>=' and f.Value >= cfxp.Value
        )         
WHERE f.GroupId = X ;

EDIT:

If you want to get persons that match all filters, just use group by:

SELECT IdPerson
FROM Person p JOIN
     CustomFieldXPerson cfxp
     on cfxp.IdPerson = p.IdPerson LEFT JOIN
     Filter f
     on f.IdField = cfxp.IdField AND
        (f.Criteria = '=' and f.Value = cfxp.Value or
         f.Criteria = '<' and f.Value < cfxp.Value or
         f.Criteria = '<=' and f.Value <= cfxp.Value or
         f.Criteria = '>' and f.Value > cfxp.Value or
         f.Criteria = '>=' and f.Value >= cfxp.Value
        ) AND      
        f.GroupId = X
GROUP BY idPerson
HAVING COUNT(f.IdField) = COUNT(*) OR COUNT(f.IdField) = 0;

In other words, all the filters that match the person, in the group, match. The additional condition in the HAVING clause is in case the filter group has no filters.

Upvotes: 2

Related Questions