We help IT Professionals succeed at work.

Need Help Pulling MySQL Data from Mutiple Dates

dannyg280
dannyg280 asked
on
Medium Priority
206 Views
Last Modified: 2012-06-22
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
Comment
Watch Question

Move this T6 from clause before the T8:

Left Outer Join arinvdet T6 On T8.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID
So it becomes:
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
Oh and your T7 before your T9:

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
Actually, leave the T7 after the T9.  So back to:

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

Author

Commented:
I then get a join error for T8. Could it have something to do with the identical joins using ARInvcID? I just don't know how to get around it.
It's the order of the join.  Before the T8 join referenced T6 before T6 was assigned.  Now T8 is the problem because T6 uses T8 before it is assigned (I missed that).

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
Oops there was an extra space after the table name.  Should have been:

Left Outer Join arinvdet T6 On arinvhed.ARInvcID = T6.ARInvcID and T4.ItemID = T6.ItemID and T5.ItemSubCatID = T6.ItemSubCatID
Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Assigned = aliased
Unlock the solution to this question.
Thanks for using Experts Exchange.

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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.