title: Search 2 postgres array_agg to find same item(s)
body: <p>I have 2 array_agg, a "search criteria" and a "list of all":</p>
  
  <p>The codes I use to get the array_agg:</p>
  
  <p>"search criteria"</p>
  
  <pre><code>SELECT ARRAY_AGG(DISTINCT n_id) as agg_n FROM pn WHERE n_id IN (100,101)
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>{100,101}
  </code></pre>
  
  <p>"list of all"</p>
  
  <pre><code>SELECT p_id, ARRAY_AGG(DISTINCT n_id) AS agg_n_2 FROM pn GROUP BY p_id
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>1 | {100,101}
  2 | {100,101,102}
  3 | {100,102}
  4 | {101,103}
  5 | {100,101,102,103}
  </code></pre>
  
  <p>The subqueries works fine and well. If I ask for it to return all of p_id that has n_id both 100 AND 101 (as per the request). It will only return <code>1</code>, when it should return <code>1,2,5</code>. If my request was just <code>{100}</code>, it will not return anything and I need it to return <code>1,2,3,5</code></p>
  
  <p>The full SQL is below. How can I do what I need? I know that the first 2 subqueries above should be correct but how do I structure the overall query?</p>
  
  <pre><code>WITH pn_o AS (SELECT p_id, ARRAY_AGG(DISTINCT n_id) AS agg_n FROM pn GROUP BY p_id),
        n_o AS (SELECT ARRAY_AGG(DISTINCT n_id) as agg_n_2 FROM pn WHERE n_id IN (100)) 
  SELECT p_id FROM pn WHERE pn_o.agg_n = (SELECT n.agg_n_2 FROM n_o);
  </code></pre>
  
  <p>I've tried = and I've tried IN (which throws an error). I can't use ANY because it is not an OR criteria but an AND.</p>
  
  <p>An example of the pn table (it has only 2 columns and both n_id and p_id are different foreign keys):</p>
  
  <pre><code>p_id | n_id
  -----+-----
  100  | 100
  100  | 101
  100  | 102
  100  | 103
  101  | 100
  101  | 102
  101  | 105
  </code></pre>
  
  <p>What I'm trying to find is the p_id that has association with the n_id be it 1 or more n_ids.</p>
  
  <p>If there is a better statement I can use, it would be great too.</p>
  
  <p>Thanks.
  Desmond</p>