Re: Query with multiple tables

From: Don (donw_at_mweco.com)
Date: 10/19/04 

Date: Tue, 19 Oct 2004 14:49:35 -0500

Don <donw_at_mweco.com> wrote in news:Xns95879415ED552donwmwecocom@
216.196.97.142:

> 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
>
I had a typo error,

The line:(select distinct sum (t3.sales from t3

Should be:(select distinct sum (t3.sales from t3)
Don

Loading