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

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

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.