SQL Union/Join 1

[davidmckee]

Not sure how to do this have two table and trying to bring in data on one report

Table 1
Item Quantity On Hand
123 5

Table 2
Item Sales Date
123 1 01/01/09
123 1 02/01/10
123 1 03/01/10

Create (for last 6 months only)
Item Quantity On Hand Sales
123 5 2

Any help appreciated

 

[Emtucifor]

SELECT
   Table1.Item,
   Table1.QuantityOnHand,
   Nz(X.SumOfSales, 0) AS Sales
FROM
   (Table1
   LEFT JOIN (
SELECT Item, Sum(Sales) AS SumOfSales WHERE [Date] >= DateAdd("m", -6, Date())
   ) AS X ON (Table1.Item = X.Item))

If that doesn't work you'll have to move the derived table query to its own query and join to that instead.

 

[davidmckee]

Sorry what about table 2 in the code? I only see table 1 in the FROM

 

[PHV]

Why not simply this ?

SELECT A.Item, A.[Quantity On Hand], SUM(B.Sales) AS SumOfSales
FROM [Table 1] A INNER JOIN [Table 2] B ON A.Item = B.Item
WHERE B.Date >= DateAdd("m",-6,Date())
GROUP BY A.Item, A.[Quantity On Hand]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Emtucifor]

My apologies. I left out a FROM clause:

SELECT
   Table1.Item,
   Table1.QuantityOnHand,
   Nz(X.SumOfSales, 0) AS Sales
FROM
   (Table1
   LEFT JOIN (
      SELECT Item, Sum(Sales) AS SumOfSales
      FROM Table2
      WHERE [Date] >= DateAdd("m", -6, Date())
   ) AS X ON (Table1.Item = X.Item))

PHV, the reason why not simply that was just to set up good coding habits. All too often people take a huge query with many joins and make it an aggregate, when only the aggregated data should be in a separate derived table.

Also, your query with an INNER JOIN will remove all the items that haven't been sold yet, which is incorrect.

The moment davidmckee adds many other columns from Table1 or joins other tables to the query, your query will not serve well.

 

[Emtucifor]

P.S. I think derived table aliases may not accept "AS", like so:

) X ON (Table1.Item = X.Item))