Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Solved

Posted on 2011-04-28

I have a query where I am trying to pull data from a DB that houses deltailed customer Invoice History. The issue I am having is I need each row of the result to produce data for 2008, 2009, and 2010. When I try to run the query I get an invalid join error message for T6. Can anyone assist?

SELECT

T1.InvoiceNum As InvoiceNum,

T2.CustAcct,

T3.SmanCode,

T4.ItemCode,

T.Description,

T5.SubCatCode As SubCatCode,

T5.SubCatDescription As SubCatDescription,

SUM(T.QtyShipped) as 'Qty08',

SUM(T.Amount) as 'Sales08',

SUM(T6.QtyShipped) as 'Qty09',

SUM(T6.Amount) as 'Sales09',

SUM(T7.QtyShipped) as 'Qty2010',

SUM(T7.Amount) as 'Sales2010'

FROM arinvdet T

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

WHERE T2.CustAcct ='75^'

and T4.ItemCode <>''

and T1.InvoiceDate Between '2008-1-1' and '2008-12-31'

and T8.InvoiceDate Between '2009-1-1' and '2009-12-31'

and T9.InvoiceDate Between '2010-1-1' and '2010-12-31'

Group By t4.ItemCode

SELECT

T1.InvoiceNum As InvoiceNum,

T2.CustAcct,

T3.SmanCode,

T4.ItemCode,

T.Description,

T5.SubCatCode As SubCatCode,

T5.SubCatDescription As SubCatDescription,

SUM(T.QtyShipped) as 'Qty08',

SUM(T.Amount) as 'Sales08',

SUM(T6.QtyShipped) as 'Qty09',

SUM(T6.Amount) as 'Sales09',

SUM(T7.QtyShipped) as 'Qty2010',

SUM(T7.Amount) as 'Sales2010'

FROM arinvdet T

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

WHERE T2.CustAcct ='75^'

and T4.ItemCode <>''

and T1.InvoiceDate Between '2008-1-1' and '2008-12-31'

and T8.InvoiceDate Between '2009-1-1' and '2009-12-31'

and T9.InvoiceDate Between '2010-1-1' and '2010-12-31'

Group By t4.ItemCode

9 Comments

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

FROM arinvdet T

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

FROM arinvdet T

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

FROM arinvdet T

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

Try using the name of the table so its:

Left Outer Join arinvhed T1 On T.ARInvcID = T1.ARInvcID

Left Outer Join arcusts T2 On T1.CustID = T2.CustID

Left Outer Join arsmen T3 On T1.SmanID = T3.SmanID

Left Outer Join icitems T4 On T.ItemID = T4.ItemID

Left Outer Join icsubcat T5 On T.ItemSubCatID = T5.ItemSubCatID

Left Outer Join arinvdet T6 On arinvhed .ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

Left Outer Join arinvhed T8 On T6.ARInvcID = T8.ARInvcID

Left Outer Join arinvhed T9 On T7.ARInvcID = T9.ARInvcID

Left Outer Join arinvdet T7 On T9.ARInvcID = T7.ARInvcID and T4.ItemID = T7.ItemID and T5.ItemSubCatID = T7.ItemSubCatID

Left Outer Join arinvdet T6 On arinvhed.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID

```
SELECT T1.InvoiceNum AS InvoiceNum,
T2.CustAcct,
T3.SmanCode,
T4.ItemCode,
T.DESCRIPTION,
T5.SubCatCode AS SubCatCode,
T5.SubCatDescription AS SubCatDescription,
SUM(T.QtyShipped) AS 'Qty08',
SUM(T.Amount) AS 'Sales08',
SUM(T6.QtyShipped) AS 'Qty09',
SUM(T6.Amount) AS 'Sales09',
SUM(T7.QtyShipped) AS 'Qty2010',
SUM(T7.Amount) AS 'Sales2010'
FROM arinvdet T
LEFT OUTER JOIN arinvhed T1
ON T.ARInvcID = T1.ARInvcID
LEFT OUTER JOIN arcusts T2
ON T1.CustID = T2.CustID
LEFT OUTER JOIN arsmen T3
ON T1.SmanID = T3.SmanID
LEFT OUTER JOIN icitems T4
ON T.ItemID = T4.ItemID
LEFT OUTER JOIN icsubcat T5
ON T.ItemSubCatID = T5.ItemSubCatID
LEFT OUTER JOIN arinvdet T6
ON T4.ItemID = T6.ItemID
AND T5.ItemSubCatID = T6.ItemSubCatID
LEFT OUTER JOIN arinvhed T8
ON T6.ARInvcID = T8.ARInvcID
LEFT OUTER JOIN arinvdet T7
ON T4.ItemID = T7.ItemID
AND T5.ItemSubCatID = T7.ItemSubCatID
LEFT OUTER JOIN arinvhed T9
ON T7.ARInvcID = T9.ARInvcID
WHERE T2.CustAcct = '75^'
AND T4.ItemCode <> ''
AND T1.InvoiceDate BETWEEN '2008-1-1' AND '2008-12-31'
AND T8.InvoiceDate BETWEEN '2009-1-1' AND '2009-12-31'
AND T9.InvoiceDate BETWEEN '2010-1-1' AND '2010-12-31'
GROUP BY t4.ItemCode
```

Title | # Comments | Views | Activity |
---|---|---|---|

OPENDATASOURCE | 8 | 25 | |

SQL help | 5 | 42 | |

Need help with IIf query/formula in Access | 6 | 29 | |

Oracle SQL Select Statement | 19 | 45 |

This video discusses moving either the default database or any database to a new volume.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!