[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Need Help Pulling MySQL Data from Mutiple Dates

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
0
dannyg280
Asked:
dannyg280
  • 7
1 Solution
 
Bryan ButlerCommented:
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
0
 
Bryan ButlerCommented:
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
0
 
Bryan ButlerCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bryan ButlerCommented:
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
0
 
dannyg280Author 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.
0
 
Bryan ButlerCommented:
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
0
 
Bryan ButlerCommented:
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
0
 
SharathData EngineerCommented:
check this.
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

Open in new window

0
 
Bryan ButlerCommented:
Assigned = aliased
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now