Query with multiple tables

• Previous message: Ed MacAlmon: "SQL Query Help"
• Next in thread: Don: "Re: Query with multiple tables"
• Reply: Don: "Re: Query with multiple tables"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 19 Oct 2004 14:33:02 -0500

I have what is probably a simple query, but I can't get anything to
work.

I have three tables.

Table one(t1).

Item, Description, product group, ........
111 My item 43
222 Another item 44

Table two(t2):

Item, location, qty_on_hand
111 1 12
111 2 4
222 1 20
222 2 10

Table three(t3):

Item, location, year, month, sales
111 1 2004 1 10.00
111 1 2004 2 20.00
111 1 2004 3 30.00
111 2 2004 1 1.00
111 2 2004 2 2.00
111 2 2004 3 3.00
222 1 2004 1 1.00
222 1 2004 2 2.00
222 1 2004 3 3.00

For each item in table one, there could be many records in table two and
many in table 3.

Looking for a query that will return.

Item Description Location YTD SALES QTY ON HAND
111 My item 1 60.00 12
111 My item 2 6.00 4
222 Another item 1 6.00 20
222 Another item 2 0 10

Seems like it should be simple, but I am stuck. There are obviously
many more items, a few more locations,
and last year and this year in table 3. I am getting very large numbers
in my YTD SALES fields.

The basics of the statement I am using is like this.

Select distinct
t1.item, t1.description, t2.location,
(select distinct sum (t3.sales from t3
  join t3 on t1.item = t3.item
  join t2 on t1.item = t2.item
  where t3.year = '2004' and t3.year = t2.location) as 'YTD SALES',
t2.qty_on_hand as 'QTY ON HAND'

Any help would be appreciated.

Thanks, Don

• Previous message: Ed MacAlmon: "SQL Query Help"
• Next in thread: Don: "Re: Query with multiple tables"
• Reply: Don: "Re: Query with multiple tables"
• Messages sorted by: [ date ] [ thread ]