title: How to write proper insert query with select subquery returning more than one row?
body: <p>I have a problem with SQL. I looked all over stackoverflow and didn't find a solution for my problem. I have an insert statement which selects from different table where in "where" statement it looks in different table, etc.</p>
  
  <p>There is a problem, that one of the subqueries return more than one value. What I need to achieve: there will be inserted two (or more, depends how much it will return) rows in the designated table.</p>
  
  <p>Example with error, what I'mtrying to achieve:</p>
  
  <pre><code>create database tst;
  /*1*/
  create table person_account_table(
  per_id varchar(50),
  acct_id varchar(50)
  );
  insert into person_account_table(per_id, acct_id)
  values('123','123');
  insert into person_account_table(per_id, acct_id)
  values('321','321');
  insert into person_account_table(per_id, acct_id)
  values('321','363');
  /*1*/
  
  /*2*/
  create table person_id_table(
  per_id varchar(50),
  per_nbr varchar(50),
  type_cd varchar(50)
  );
  insert into person_id_table(per_id, per_nbr, type_cd)
  values ('123', 'zx32', 'good');
  insert into person_id_table(per_id, per_nbr, type_cd)
  values ('123', '32zx', 'pklx');
  insert into person_id_table(per_id, per_nbr, type_cd)
  values ('321', '35xcz', 'good');
  insert into person_id_table(per_id, per_nbr, type_cd)
  values ('321', 'fes235', 'pklx');
  /*2*/
  
  /*3*/
  create table table_one(
  au_id varchar(50),
  type_cd varchar(50),
  acct_id varchar(50)
  );
  insert into table_one(au_id, type_cd, acct_id)
  values('1', 'e-pg', '321');
  insert into table_one(au_id, type_cd, acct_id)
  values('2', 'e-pg', '363');
  insert into table_one(au_id, type_cd, acct_id)
  values('3', 'e-pg', '123');
  /*3*/
  
  /*4*/
  create table table_two(
  per_nbr varchar(50),
  ob_nbr varchar(50),
  flag varchar(1)
  );
  insert into table_two(per_nbr, ob_nbr, flag)
  values('zx32', 'dfas', 'N');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('zx32', 'dfsvgd', 'P');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('zx32', 'dsfds', 'N');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('zx32', 'sdfdsf', 'P');
  
  insert into table_two(per_nbr, ob_nbr, flag)
  values('35xcz', 'dhf', 'N');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('35xcz', 'tes', 'N');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('35xcz', 'dfgdf', 'P');
  insert into table_two(per_nbr, ob_nbr, flag)
  values('35xcz', 'ehdhs', 'P');
  /*4*/
  
  -- table in which I want to inser data
  create table result_table(
  obj_nbr varchar(50),
  au_id varchar(50)
  );
  </code></pre>
  
  <p><strong>insert statement:</strong></p>
  
  <pre><code>insert into result_table (obj_nbr, au_id)
  select tt.ob_nbr,
  (
      select to1.au_id 
      from table_one to1 
      where to1.acct_id in
          (
              select pat.acct_id
              from person_account_table pat
              where pat.per_id in
              (
                  select pit.per_id
                  from person_id_table pit
                  where pit.per_nbr = tt.per_nbr and
                  pit.type_cd in ('good')
              )
          )
  )
  from table_two tt
  where tt.flag = 'N';
  </code></pre>
  
  <p>And I get in result: <strong>Subquery returns more than 1 row</strong></p>
  
  <p>In this particular example, I get this error, because per_id = 321 has two different account. And each account gets it's own au_id from table_one.
  How can I rewrite this query, that it will not crash, but insert two rows in the result table?</p>