title: Oracle SQL finding matching records on multiple sub queires in multiple tables
body: <p>There are three tables:  CLIENT, MEMBER, and MEMBER_INVOICE.  CLIENT can have many members and member can have many invoices.  I am trying to see how many overall clients overall have the total number of invoices, to where the desired output would be:</p>
  
  <pre><code>CLIENT, TOTAL INVOICE
  client a, 4
  client b, 11
  </code></pre>
  
  <p>The basic table structure is:</p>
  
  <p>CLIENT</p>
  
  <pre><code>Name               Null?    Type               
  ------------------ -------- ------------------ 
  ID                 NOT NULL NUMBER(38)         
  CLIENT_NAME                 VARCHAR2(255 CHAR) 
  BILLING_ID                  VARCHAR2(255 CHAR)
  </code></pre>
  
  <p>MEMBER</p>
  
  <pre><code>Name                        Null?    Type               
  --------------------------- -------- ------------------ 
  ID                          NOT NULL  NUMBER(38)         
  MEMBER_NAME                           VARCHAR2(255 CHAR) 
  BILLING_ID                            VARCHAR2(255 CHAR)
  CLIENT_ID                             NUMBER(38) 
  </code></pre>
  
  <p>NOTE:  CLIENT_ID is tied to the ID of the CLIENT record (I inherited this database and design).</p>
  
  <p>MEMBER_INVOICE</p>
  
  <pre><code>--------------- -------- ------------ 
  ID              NOT NULL NUMBER(38)   
  MEMBER_ID                NUMBER(38)   
  INVOICE_ID               NUMBER(38) 
  </code></pre>
  
  <p>MEMBER_INVOICE is a lookup to the INVOICE and MEMBER table</p>
  
  <p>there is a client_id column on member, and a member_id record on the invoice.  I've tried to break it out into individual queries, but i'm getting very confused.  This is what I've been working on so far</p>
  
  <p>query 1</p>
  
  <pre><code>select client_name, billing_id from client WHERE  REGEXP_LIKE(billing_id, '^[[:digit:]]+$');   //gives the unique billing id to search the members table
  </code></pre>
  
  <p>query 2</p>
  
  <pre><code>select a.member_name,
         count(*)
  from member a
    LEFT JOIN member_invoices b ON a.id= b.member_id
  group by a.member_name;     // works -- gives me a list of the MEMBER names and the total number of INVOICES for each
  </code></pre>
  
  <p>Since CLIENT can have many MEMBER, I need to plug in query 2 from the above statement, to somehow get a list of the clients name and the total number of invoices under the client's total members. </p>