title: SQL query - need to exclude if Requirement NOT met, and exclude if Disqualifier IS met
body: <p>I have a feeling once i see the solution i'll slap my forehead, but right now I'm not seeing it.</p>
  
  <p>I have a lookup table, say <code>TableB</code>, which looks like this. All fields are <code>INT</code> except the last two which are BOOL.</p>
  
  <pre><code>ID, TableA_ID, Value, Required, Disqualifies
  </code></pre>
  
  <p>I have a list of <code>TableA_Id</code> values <code>(1, 2, 3 )</code> etc </p>
  
  <p>For each record in this table, either Required can be true or disqualified can be true - they cant both be true at the same time. They can both be false or null though. There can be duplicate values of <code>TableA_Id</code> but there should never be duplicates of <code>TableA_Id</code> and <code>Value</code></p>
  
  <p>If required is true for any of those TableA_ID values, and none of those values are in my list, return no records. If none of the values are marked as required <code>(required = 0 or null)</code> then return records <strong>UNLESS</strong> any of the values are marked as Disqualifies and are in the list, in which case i want to return no records.</p>
  
  <p>So - if a field is required and i dont have it, dont return any records. If a field is marked as disqualified and i have it, don't return any records. Only return a record if either i have a required value or don't have a disqualified value or there are no required values.</p>
  
  <p>I hope I explained myself clearly. </p>
  
  <p>Thanks in advance for pointing me in the right direction.</p>
  
  <p>As an example of what my records might look like:</p>
  
  <pre><code>ID  TableA_ID  Value  Required  Disqualifies
  --  ---------  -----  --------  ------------
  1   123        1      True      False
  2   123        2      True      False
  3   123        3      False     False
  4   123        4      False     True
  5   456        1      False     True
  6   456        2      False     False
  </code></pre>
  
  <p>Given this set of sample data, if we're working with <code>TableA_Id</code> 123 and my list of values is lets say 1 and 3, i would get data returned because i have a required value and dont have any disqualified values. If my list of values were just 3, i'd get no records since i'm missing of the Required values. If my list of values were 1 and 4, i'd get no records because 4 is marked as disqualified. </p>
  
  <p>Now if we're working with <code>TableA_Id</code> 456, the only list of values that would return any records is 2. </p>
  
  <p>Maybe i should post the whole SQL query - i was trying to keep this short to make it easier for everyone, but it looks like maybe that's not working so well.</p>
  
  <p>Here is the full dynamically generated query. The bit i am working on now is the 2nd line from the bottom. To equate this to my example, <code>t.id</code> would be <code>TableA_ID</code>, <code>Value</code> would be <code>PDT_ID</code>.</p>
  
  <pre><code>SELECT DISTINCT t.ID, t.BriefTitle, stat.Status, lstat.Status AS LocationStatus, st.SType, t.LAgency, l.City, state.StateCode
  ,( SELECT TOP 1 UserID  
  FROM TRecruiter 
  WHERE TrialID = t.ID AND Lead = 1 ), l.ID as LocationID
  , l.WebBased 
  FROM Trial t 
  INNER JOIN Location l ON t.ID = l.TrialID       
  FULL JOIN pdt on t.ID = pdt.trialid       
  FULL JOIN pdm on t.ID = pdm.TrialID       
  FULL JOIN s on t.ID = s.TrialID       
  FULL JOIN hy on t.ID = hy.TrialID       
  FULL JOIN ta on t.ID = ta.TrialID       
  FULL JOIN stt on t.ID = stt.TrialID       
  FULL JOIN [Status] stat ON t.StatusID = stat.ID       
  FULL JOIN st ON t.StudyTypeID = st.ID       
  FULL JOIN State state ON l.StateID = state.ID       
  FULL JOIN [Status] lstat ON l.StatusID = lstat.ID       
  FULL JOIN ts ON t.ID = ts.TrialID       
  FULL JOIN tpdm ON t.ID = tpdm.TrialID      
  WHERE ((t.ID IS NOT NULL) 
  AND (EligibleHealthyVolunteers IS NULL OR EligibleHealthyVolunteers = 1 OR (0 = 0 AND EligibleHealthyVolunteers = 0)) 
  AND (eligiblegenderid is null OR eligiblegenderid = 1 OR eligiblegenderid = 3) 
  AND ((EligibleMinAge &lt;= 28 AND EligibleMaxAge &gt;= 28) OR (EligibleMinAge &lt;= 28 AND EligibleMaxAge is null) OR (EligibleMinAge IS NULL AND EligibleMaxAge &gt;= 28)) 
  AND (HYID = 6 AND (hy.Disqualify = 0 OR hy.Disqualify IS NULL AND NOT EXISTS (SELECT * FROM hy WHERE t.id = hy.TrialID AND hy.Req =1)) OR HYID = 6 AND hy.req = 1)
  AND (PDT_ID IN (1) AND ( pdt.Disqualify = 0 OR pdt.Disqualify IS NULL AND NOT EXISTS (select * from pdt where t.id = pdt.TrialID AND pdt.Req = 1)) OR PDT_ID IN (1) AND (pdt.Req = 1 AND (pdt.Disqualify = 0 or pdt.Disqualify is null )))      
  ) AND ((3959 * acos(cos(radians(34.18)) * cos(radians(l.Latitude)) * cos(radians(l.Longitude) - radians(-118.46)) + sin(radians(34.18)) * sin(radians(l.Latitude)))) &lt;= 300 OR l.Latitude IS NULL) AND t.IsPublished = 1 AND (t.StatusID = 1 OR t.StatusID = 2) 
  </code></pre>
  
  <p>I've changed/shortened some table names just for security/privacy reasons. </p>
  
  <p><strong>Edit:</strong>
  I think i am close to getting this working, but I'm getting tripped up on the logic again.</p>
  
  <p>I have the following bit of sql:</p>
  
  <pre><code>AND ( exists (SELECT * FROM  pdt WHERE Req = 1 AND trialid = t.id AND pdT_ID IN (2) )  AND EXISTS (SELECT * FROM  pdt WHERE Req = 1 AND trialid = t.id    )     )
  </code></pre>
  
  <p>I'm not sure how to structure this. Those two exists statement should make the whole thing true in the following combination:
  True &amp; False
  True &amp; True
  False &amp; False</p>
  
  <p>If it's False &amp; True, then the whole thing is false. In other words if there is a Req =1 AND the PDT_ID that is marked as Req=1 is not in our list (in the example above the list just contains '2') then return false.</p>
  
  <p><strong>EDIT:</strong>
  I think i finally got it. </p>
  
  <pre><code>AND NOT EXISTS (SELECT * FROM  pdt WHERE Disqualify = 1 AND trialid = t.id AND  PDT_ID IN (2)  )
  AND NOT ( NOT exists (SELECT * FROM  pdt WHERE Req = 1 AND trialid = t.id AND PDT_ID IN (2) )  AND EXISTS (SELECT * FROM  pdt WHERE Req = 1 AND trialid = t.id    )  )
  </code></pre>
  
  <p>So far this seems to work in testing. Although I'm only working with two values of PDT_ID. If this does resolve my problem, i will come back and give someone the credit for helping me.</p>