Oracle SQL - Multiplewhere conditions based on each other from the same table
<p>I have a single table <code>ledrow</code> Which has 4 columns.</p>

<p>I want to only select rows where they have (per <code>vnum</code> and <code>comp</code>) rows that are in both sets of logic.</p>

<p><strong>English Explanation</strong></p>

<hr>

<p>So for example out of all the data below, explaining the rows in the expected output is as follows:</p>

<p>For <code>comp</code> 99 we have results as all of the <code>vacc</code> numbers fall into either of the sets of logic:</p>

<p>The <code>vacc</code> is either <code>BETWEEN '1544' AND '1567'</code> OR the <code>vacc</code> is <code>BETWEEN '3000' AND '3999'</code></p>

<p>However for example <code>comp</code> 77 rows aren't output because even though:</p>

<pre><code>comp vtype  vnum    vacc
77   F      1369    3400
77   F      1369    3402
</code></pre>

<p>Comes under the second part of the logic the rest of the rows don't fall under any:</p>

<pre><code>comp vtype  vnum    vacc
77   F      1369    1510
77   F      1369    2620
77   F      1369    2620
77   F      1369    2620
77   F      1369    1650
</code></pre>

<p>This is because to be part of the output I want all rows per comp MUST fall in either parts of the logic per comp.</p>

<hr>

<p>I have tried the following code but cannot get my expected output.</p>

<p>Current Code:</p>

<pre><code>SELECT * FROM (
SELECT comp, vtype, vnum, vacc
FROM ledrow
WHERE 
((comp = '55' AND vacc BETWEEN '1544' AND '1567') AND (comp = '55' AND (vacc = '3019' OR vacc = '5222')))
OR
((comp = '66' AND vacc BETWEEN '1544' AND '1567') AND (comp = '66' AND (vacc  = '3013' OR (vacc BETWEEN '6910' AND '6973'))))
OR
((comp NOT IN ('55', '66') AND vacc BETWEEN '1544' AND '1567') AND (comp NOT IN ('55', '66') AND (vacc BETWEEN '3000' AND '3999')))) outputled
WHERE outputled.vtype = 'F'
</code></pre>

<p>Expected Output:</p>

<pre><code>comp vtype  vnum    vacc
99   F      1369    1564
99   F      1369    2610
99   F      1369    2610
99   F      1369    3601
99   F      1369    3600
</code></pre>

<p>Full Table (<code>SELECT * FROM ledrow</code>):</p>

<p><strong>ledrow</strong></p>

<pre><code>comp vtype  vnum    vacc
77   F      1369    1510
77   F      1369    2620
77   F      1369    2620
77   F      1369    2620
77   F      1369    3400
77   F      1369    3402
77   F      1369    1650
99   F      1369    1564
99   F      1369    2610
99   F      1369    2610
99   F      1369    3601
99   F      1369    3600
99   I      1369    2450
99   I      1369    2440
99   I      1369    2640
99   J      1369    5430
99   J      1369    2450
99   J      1369    5430
99   J      1369    2455
99   J      1369    5410
99   J      1369    2455
99   J      1369    5410
22   F      1369    2620
22   F      1369    3500
22   F      1369    2495
22   F      1369    1510
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    2620
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   F      1369    3500
22   F      1369    2495
22   I      1369    2450
22   I      1369    2440
22   I      1369    2640
22   J      1369    5520
22   J      1369    5520
22   J      1369    2455
22   J      1369    2450
</code></pre>

<p><strong>Edit</strong></p>

<p>So for each <code>vnum</code> and <code>comp</code> it needs to satisfy both of the two conditions in the WHERE clause.</p>

<p>So for <code>comp</code> <code>99</code> which has to satisfy both the conditions:</p>

<pre><code>((comp NOT IN ('55', '66') AND vacc BETWEEN '1544' AND '1567') AND (comp NOT IN ('55', '66') AND (vacc BETWEEN '3000' AND '3999'))))
</code></pre>