title: Create complex query to execute queries stored in column?
body: <p>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.</p>
  
  <p>Here is a screenshot of the 3 tables:</p>
  
  <p><a href="https://i.stack.imgur.com/oJ3D8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oJ3D8.png" alt="tables"></a></p>
  
  <p>So I need a query to do this:</p>
  
  <ol>
  <li>Given a Status (<code>IDStatus</code>) and an Action (<code>IDAction</code>)</li>
  <li><p>If there is an <code>IDCondition</code> (it can be <code>NULL</code>, one, or more than one)</p>
  
  <p>A. If there is one, use the <code>IDocField</code> as a column from a table called <code>IDoc</code>, using <code>ConditionOperator</code> as the condition (read this as <code>=</code>, <code>&lt;</code>, <code>&gt;</code>) and the value on <code>ConditionValue</code>.</p>
  
  <p>B. If there are more than one, the same as before, but use each condition with <code>AND</code>.</p>
  
  <p>C. If it is <code>NULL</code>, directly go to point 3</p></li>
  <li><p>Get <code>Subject</code>, <code>SendTo</code>, <code>CC</code>, <code>CCO</code>, and <code>FileTemplate</code> that matched all of the above explained.</p></li>
  </ol>
  
  <p>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.</p>
  
  <p>I am asking this, because I am not sure how to get multiple conditions in a <code>SELECT</code>. I mean by this:</p>
  
  <p>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 ? </p>
  
  <p>Example:</p>
  
  <pre><code>SELECT * 
  FROM WF_MailCondition 
  WHERE IDCondition = 1
  </code></pre>
  
  <p>this will return all conditions (keys can repeat) that has <code>IDCondition</code> equal to 1. But then I have to use all the three columns to create a condition like <code>ID = 1</code> been <code>ID</code> in <code>IDocField</code>, <code>=</code> in <code>ConditionOperator</code> and <code>1</code> in <code>ConditionValue</code>.</p>
  
  <p>I know there is some inconsistencies in the image, like <code>IDCondition</code> is <code>NN</code> (not null) but I said that it can be null (I am fixing this things).</p>
  
  <p>My SQL:</p>
  
  <pre><code>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")
  </code></pre>
  
  <p>How to do the <code>SELECT "HERE SELECT 3 COLUMNS..."</code> and if the <code>IDCondition</code> is <code>NULL</code> directly get the columns requested in the first SELECT (<code>Subject</code>, <code>SendTo</code>, etc.)</p>
  
  <p>The <code>"ACTION FROM OUTSIDE"</code> is a parametrized statement.</p>
  
  <p>Forget about foreign keys, we are not using it (boss' decisions...).</p>
  
  <p>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.</p>
  
  <p>Maybe all of this can be a function or a stored procedure but I am very ignorant about that and performance with them.</p>
  
  <p>If you need more info, please tell me.</p>