Create complex query to execute queries stored in column?

Question

I am trying to get some information from a table, when a (one or more than one) condition(s) is(are) met but this conditions are stored in other table.

Here is a screenshot of the 3 tables:

So I need a query to do this:

1. Given a Status (IDStatus) and an Action (IDAction)

2. If there is an IDCondition (it can be NULL, one, or more than one)

   A. If there is one, use the IDocField as a column from a table called IDoc, using ConditionOperator as the condition (read this as =, <, >) and the value on ConditionValue.

   B. If there are more than one, the same as before, but use each condition with AND.

   C. If it is NULL, directly go to point 3

3. Get Subject, SendTo, CC, CCO, and FileTemplate that matched all of the above explained.

I want to try this because it would reduce my C# code, but if this is too complex or not plausible, I will do multiple queries like what I explained above.

I am asking this, because I am not sure how to get multiple conditions in a SELECT. I mean by this:

If there is more than one condition, how can I execute them or get all of them in a single query but that has all the returning things on point 3 ?

Example:

SELECT * 
FROM WF_MailCondition 
WHERE IDCondition = 1

this will return all conditions (keys can repeat) that has IDCondition equal to 1. But then I have to use all the three columns to create a condition like ID = 1 been ID in IDocField, = in ConditionOperator and 1 in ConditionValue.

I know there is some inconsistencies in the image, like IDCondition is NN (not null) but I said that it can be null (I am fixing this things).

My SQL:

SELECT 
    Subject, SendTo, CC, CCO, FileTemplate 
FROM 
    WF_Mail AS M
INNER JOIN 
    WF_MailStatusAction AS S ON S.IDMail = M.IDMail
WHERE 
    (SELECT "HERE SELECT THE 3 COLUMNS TO USE A CONDITION" FROM WF_MailCondition WHERE IDCondition = S.IDCondition)
    AND (S.IDAction = "ACTION FROM OUTSIDE" AND S.IDStatus = "STATUS FROM OUTSIDE")

How to do the SELECT "HERE SELECT 3 COLUMNS..." and if the IDCondition is NULL directly get the columns requested in the first SELECT (Subject, SendTo, etc.)

The "ACTION FROM OUTSIDE" is a parametrized statement.

Forget about foreign keys, we are not using it (boss' decisions...).

I am doing this because is for an "event" thing. I call a method in my C# code that will see if there are mails to send. But this mails can have none, one or more than one conditions. This is to have more options than all have to send a mail. Maybe in the workflow there is no need to send email but in code is "hardcoded" so with this I am totally free to do what I need about that feature.

Maybe all of this can be a function or a stored procedure but I am very ignorant about that and performance with them.

If you need more info, please tell me.

Answer 1

I would suggest the following: - Create a QueryBuilder Class Where you have methods like:

* AddSelect()
* AddFrom()
* AddWhere()
* ToString()
...

And then use the "middlelayer" to handle the query cunstruction based on the conditions That way you have the most control over everything going on

Answer 2

From what I understand, I would write a query like this:

select m.subject, m.cc 
from wf_mail m
where exists (select 'true' 
            from wf_mailstatusaction s, wf_mailcondition c
            where s.idmail=m.idmail
            and s.idcondition = c.idcondition
            and c.idocfield||c.conditionoperator||c.conditionValue = 'id>500'
            group by s.idcondition
            having count(s.idcondition) = 1)
or exists (select 'true' 
            from wf_mailstatusaction s, wf_mailcondition c
            where s.idmail=m.idmail
            and s.idcondition = c.idcondition
            and c.idocfield||c.conditionoperator||c.conditionValue IN ('id>500', 'id<200')
            group by s.idcondition
            having count(s.idcondition) > 1)
or exists (select 'true'
            from wf_mailstatusaction s
            where s.idmail=m.idmail
            and s.idcondition is null)