I have two tables/views that I am trying to join properly. They are V_ARINVOICE_ALL and V_GLBATCH_AR_COGS. The third table V_ARINVOICE_ALL in the following image is just for extra explanation.
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 Credit from V_GLBATCH_AR_COGS.
I use the following SQL query to join V_ARINVOICE_ALL and V_ARINVOICE_ALL and I get the all the right results except for the `Credit which isn't selected:
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 >= TO_DATE('05/04/2016', 'dd/mm/yyyy')
AND
V_ARINVOICE_ALL.INVOICE_DATE <= TO_DATE('15/06/2017', 'dd/mm/yyyy')
ORDER BY
V_ARINVOICE_ALL.INVOICE_DATE
Result (41 Rows)
|INVOICE_DATE|...| INVOICE|
+------------+---+--------+
| 05/04/2016|...| 494658|
| 20/05/2016|...| 495274|
| 20/05/2016|...| 495275|
But when I try and join V_ARINVOICE_ALL and V_GLBATCH_AR_COGS with the following query:
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 >= TO_DATE('05/04/2016', 'dd/mm/yyyy')
AND
V_ARINVOICE_ALL.INVOICE_DATE <= TO_DATE('15/06/2017', 'dd/mm/yyyy')
ORDER BY
V_ARINVOICE_ALL.INVOICE_DATE
I get something like: (165 Rows)
|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|
| ... |...| ... | ... |
Desired result (41 Rows)
|INVOICE_DATE|...| INVOICE| INCOME|
+------------+---+--------+--------+
| ... |...| ... | ... |
| 23/06/2016|...| 495667| 225|
| 23/06/2016|...| 495667| 225|
| 23/06/2016|...| 495667| 450|
| ... |...| ... | ... |
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?
EDIT2: First edit was bad made no sense. I don't know if this helps but there is only two rows that would be
| 23/06/2016|...| 495667| 225|
| 23/06/2016|...| 495667| 225|
in the table which match the WHERE clause on the Join : 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
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.
If that's any help.
If you need more info feel free to ask.
Looks like a one to many relationship. There are more values in the COGS table than you require (likely another column filled with other data).
Try SELECT DISTINCT to limit to unique values on selected columns:
SELECT DISTINCT -- DISTINCT goes here
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 >= TO_DATE('05/04/2016', 'dd/mm/yyyy')
AND
V_ARINVOICE_ALL.INVOICE_DATE <= TO_DATE('15/06/2017', 'dd/mm/yyyy')
ORDER BY
V_ARINVOICE_ALL.INVOICE_DATE