title: Complex SQL involving LOOP and QUERY
body: <p>I have a result of some query that has only one column and in this column I need pass row by row making a query of this row content and where on the end I need a table with all result of this queries that I made row by row</p>
  
  <p>I'm using SQL Developer with connection on a Oracle DB</p>
  
  <p>this reproduces my first select</p>
  
  <pre><code>SELECT DISTINCT REGEXP_SUBSTR ('5256,5257,5258,5259','[^,]+',1,LEVEL) NUM_PROD
  FROM dual CONNECT BY REGEXP_SUBSTR ('5256,5257,5258,5259','[^,]+',1,LEVEL) IS NOT NULL
  </code></pre>
  
  <p>I need get for example this content 5256, and use in another query.</p>
  
  <p>That will return a result like this:</p>
  
  <pre><code>NAME      | NUM_PROD | COLUMN1 | COLUMN2
  
  Michael | 5256 | AA | BB
  </code></pre>
  
  <p>after the result of all this query need be like </p>
  
  <pre><code>Michael | 5256 | AA | BB
  
  Rachel  | 5257| AA | BB
  
  Jony      | 5258| AA | CC
  </code></pre>
  
  <p>Thank you for helping.</p>
  
  <p>EDIT:</p>
  
  <p>Let me explain better how my structure works</p>
  
  <pre><code>create table tableA (cod_person, nam_person, num_atend, num_presc, columnX, ColumnY) as (
      select 100, 'Michael', 3300, 2215, 'XX', 'YY' from dual union all
      select 101, 'Rachel',  3301, 2351, 'XX', 'YY' from dual union all
      select 103, 'Jony',    3302, 2463, 'XX', 'YY' from dual union all
      select 104, 'Tony',    3303, 2235, 'XX', 'YY' from dual);
  
  
  create table tableB (num_presc, num_seq, columnXX, ColumnYY) as (
      select 2215, 2332,'XX', 'YY' from dual union all
      select 2351, 2334,'XX', 'YY' from dual union all
      select 1531, 2345,'XX', 'YY' from dual union all
      select 3250, 2348,'XX', 'YY' from dual);
  
  
  create table tableC (num_presc, num_prod, num_seq_mat, columnXXX, ColumnYYY) as (
      select 2215, 5256, 2332,'XX', 'YY' from dual union all
      select 1205, 5252, 2337,'XX', 'YY' from dual union all
      select 2351, 5258, 2334,'XX', 'YY' from dual union all
      select 3135, 5260, 2349,'XX', 'YY' from dual);
  </code></pre>
  
  <p>My query need be like </p>
  
  <pre><code>SELECT
      aa.cod_person,
      aa.nam_person,
      aa.num_atend,
      num_prod
  FROM (SELECT DISTINCT regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) num_prod FROM dual CONNECT BY regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) IS NOT NULL)
      left join aa using (tableA)
      left join bb using (tableB)
      left join cc using (tableC)
  WHERE   bb.num_presc = cc.num_presc 
  AND     bb.num_seq = cc.num_seq_mat
  AND     cc.num_prod =  (HERE WHEN I AM QUERYING ONE BY ONE I PUT THE NUM_PROD BUT NOW I'M SEARCHING ALL IN ONE TIME, SO, I DONT KNOW IF NEEDED THIS ARGUMENT)
  AND     aa.num_presc = bb.num_presc
  group by aa.cod_person, aa.num_atend
  </code></pre>
  
  <p>Currently I do this query one by one and work with this code:</p>
  
  <pre><code>SELECT
      aa.cod_person,
      aa.nam_person,
      aa.num_atend,
      5256 num_prod
  FROM    tableA aa,
          tableB bb,
          tableC cc
  WHERE   bb.num_presc = cc.num_presc 
  AND     bb.num_seq = cc.num_seq_mat
  AND     cc.num_prod =  5256
  AND     aa.num_presc = bb.num_presc
  group by aa.cod_person, aa.num_atend
  </code></pre>
  
  <p>but I need all the results together</p>