title: Select rows with the same foreign key but another column has a set of values
body: <p>I have designed a database that stores data in a format like this where both columns are foreign keys leading to different tables. This is a simplified version.</p>
  
  <pre><code>RNA_id | Experiment_id |
     1   |       a       |
     1   |       b       |
     2   |       a       |
     2   |       b       |
     2   |       c       |
     3   |       b       |
     4   |       a       |
     4   |       c       |
  </code></pre>
  
  <p>I want to select rows that have all three experiment IDs. In this example the results should be</p>
  
  <pre><code>RNA_id | Experiment_id |
     2   |       a       |
     2   |       b       |
     2   |       c       |
  </code></pre>
  
  <p>I tried </p>
  
  <pre><code>GROUP BY RNA_id HAVING COUNT (DISTINCT Experiment_id)=3
  </code></pre>
  
  <p>but that just resulted in me having a seemingly random assortment of rows.</p>
  
  <p>The database is already quite large and my query as is is fairly complex already, additionally, I may possibly want to expand to 4 or more Experiment_ids.</p>
  
  <p>Here is a sanitized version of my query as it is complicated and I don't want to explain my whole data structure</p>
  
  <p>My existing query is:</p>
  
  <pre><code>SELECT RNA_id, Experiment_id, &lt;data values&gt;
  FROM data
  LEFT JOIN ref1
  LEFT JOIN ref2
  LEFT JOIN ref3
  LEFT JOIN ref4
  WHERE &lt;required data parameters&gt;
  ORDER BY RNA_id
  LIMIT 0,5000;
  </code></pre>
  
  <p>Returns about 700 values, as it should,
  but when I change ORDER BY to that GROUP BY command earlier, it returns 9 values all of which have unique RNA_ids and there should be about 100 that get returned.</p>
  
  <p>My RNA_id is actually 3 columns so I may just have to redo the entire database to get any of these solutions to work. </p>
  
  <h1> My Solution</h1>
  
  <p>I figured out how to make it work with the 3 column identifier</p>
  
  <pre><code>AND (RNA_id1, RNA_id2, RNA_id3) IN (SELECT RNA_id1, RNA_id2, RNA_id3
  FROM data
  WHERE &lt;parameter&gt;
  GROUP BY RNA_id1, RNA_id2, RNA_id3
  HAVING COUNT (DISTINCT Experiment_id)=3)
  </code></pre>
  
  <p>This works with different numbers of Experiment_ids too</p>