title: Oracle SQL Join two tables/views avoid cross product
body: <p>I have two tables/views that I am trying to join properly. They are <code>V_ARINVOICE_ALL</code> and <code>V_GLBATCH_AR_COGS</code>. The third table <code>V_ARINVOICE_ALL</code> in the following image is just for extra explanation. </p>
  
  <p><a href="https://i.stack.imgur.com/USv2p.png" rel="nofollow noreferrer">Click Here</a></p>
  
  <p>I'm currently getting the wrong amount of rows being returned because of, I think, kind of a cross join. I am trying to join <code>Credit</code> from <code>V_GLBATCH_AR_COGS</code>.</p>
  
  <p>I use the following SQL query to join <code>V_ARINVOICE_ALL</code> and <code>V_ARINVOICE_ALL</code> and I get the all the right results except for the `Credit which isn't selected:</p>
  
  <pre><code>SELECT 
  V_ARINVOICE_ALL.INVOICE_DATE,
  V_ARINVOICE_ALL.ARCUSTO_COMPANY,
  V_ARINVOICE_ALL.ARCUSTO_CUSTNO,
  V_ARINVOICE_ALL.INVOICE_NO
  
  
  FROM   
  IQMS.V_ARINVOICE_ALL V_ARINVOICE_ALL 
  
  
  INNER JOIN
  IQMS.V_GLACCT V_GLACCT
  ON
    V_ARINVOICE_ALL.GLACCT_ID_SALES = V_GLACCT.ID AND V_GLACCT.ACCT = '3648-00-0'
  
  
  
  WHERE 
  V_ARINVOICE_ALL.ARCUSTO_CUSTNO = 'OX01' 
  AND 
  V_ARINVOICE_ALL.INVOICE_DATE &gt;= TO_DATE('05/04/2016', 'dd/mm/yyyy')
  AND
  V_ARINVOICE_ALL.INVOICE_DATE &lt;= TO_DATE('15/06/2017', 'dd/mm/yyyy')
  
  ORDER BY 
   V_ARINVOICE_ALL.INVOICE_DATE 
  </code></pre>
  
  <blockquote>
    <p>Result (41 Rows)</p>
  </blockquote>
  
  <pre><code>  |INVOICE_DATE|...| INVOICE|
    +------------+---+--------+
    |  05/04/2016|...|  494658|
    |  20/05/2016|...|  495274|
    |  20/05/2016|...|  495275|
  </code></pre>
  
  <p>But when I try and join <code>V_ARINVOICE_ALL</code> and <code>V_GLBATCH_AR_COGS</code> with the following query:</p>
  
  <pre><code>SELECT 
   V_ARINVOICE_ALL.INVOICE_DATE,
   V_ARINVOICE_ALL.ARCUSTO_COMPANY,
   V_ARINVOICE_ALL.ARCUSTO_CUSTNO,
   V_ARINVOICE_ALL.INVOICE_NO,
   V_GLBATCH_AR_COGS.CREDIT AS "INCOME"
  
  
  FROM   
   IQMS.V_ARINVOICE_ALL V_ARINVOICE_ALL 
  
  
  INNER JOIN
   IQMS.V_GLBATCH_AR_COGS V_GLBATCH_AR_COGS
  ON
   V_ARINVOICE_ALL.GLBATCHID_ID = V_GLBATCH_AR_COGS.ID
   AND V_ARINVOICE_ALL.GLPERIODS_ID_AR = V_GLBATCH_AR_COGS.GLPERIODS_ID
   AND V_GLBATCH_AR_COGS.DESCRIP LIKE '%OX01%ITEMS%' AND V_GLBATCH_AR_COGS.ACCT = '3648-00-0'
  
  
  
  WHERE 
   V_ARINVOICE_ALL.ARCUSTO_CUSTNO = 'OX01' 
  AND 
   V_ARINVOICE_ALL.INVOICE_DATE &gt;= TO_DATE('05/04/2016', 'dd/mm/yyyy')
  AND
   V_ARINVOICE_ALL.INVOICE_DATE &lt;= TO_DATE('15/06/2017', 'dd/mm/yyyy')
  
  ORDER BY 
   V_ARINVOICE_ALL.INVOICE_DATE 
  </code></pre>
  
  <blockquote>
    <p>I get something like: (165 Rows)</p>
  </blockquote>
  
  <pre><code>  |INVOICE_DATE|...| INVOICE|  INCOME|
    +------------+---+--------+--------+
    |  ...       |...|  ...   |   ...  |
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     450|
    |  23/06/2016|...|  495667|     450|
    |  23/06/2016|...|  495667|     450|
    |  ...       |...|  ...   |   ...  |
  </code></pre>
  
  <blockquote>
    <p>Desired result (41 Rows)</p>
  </blockquote>
  
  <pre><code>  |INVOICE_DATE|...| INVOICE|  INCOME|
    +------------+---+--------+--------+
    |  ...       |...|  ...   |   ...  |
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     450|
    |  ...       |...|  ...   |   ...  |
  </code></pre>
  
  <p>I'm a bit more familiar with joins than I was before. But I'm not certain I'm doing them right. I've tried right join, right outer join, left join, left outer join, etc. What am I doing wrong here?</p>
  
  <hr>
  
  <p>EDIT2: First edit was bad made no sense. I don't know if this helps but there is only two rows that would be</p>
  
  <pre><code>  |  23/06/2016|...|  495667|     225|
    |  23/06/2016|...|  495667|     225|
  </code></pre>
  
  <p>in the table which match the <code>WHERE</code> clause on the Join : <code>V_ARINVOICE_ALL.GLPERIODS_ID_AR = V_GLBATCH_AR_COGS.GLPERIODS_ID AND V_GLBATCH_AR_COGS.DESCRIP LIKE '%OX01%ITEMS%' AND V_GLBATCH_AR_COGS.ACCT = '3648-00-0</code></p>
  
  <p>But would still get it more than twice. So I imagine it has something to do with the relationship being 1 to Many. Which I'm not sure how to deal with.</p>
  
  <p>If that's any help.</p>
  
  <hr>
  
  <p>If you need more info feel free to ask.</p>