Hi all, im quite new in this program, but need some help with sql query.
Trying to display invoices for some period (for test purpose selected above no 1470)


select
tdsls401.invn, | Invoice Number
tdsls401.invd, | Invoice Date
tdsls401.item, | Item
tdsls401.damt | Amount
from
tdsls401 | Sales Order Lines
where
tdsls401.invn > 1470


This is working weel to me, i'm getting something like this:

Inv no Date Item code Amount
1470 15/09/2010 CODE1 123,00
1470 15/09/2010 CODE2 234,00
1471 15/09/2010 CODE3 345,00
1472 16/09/2010 CODE1 222,00

Then i want to join item group from another table


select
tdsls401.invn, | Invoice Number
tdsls401.invd, | Invoice Date
tdsls401.item, | Item
tdsls411.item, | Item
tdsls411.citg, | Item Group
tdsls401.damt | Amount
from
tdsls401, | Sales Order Lines
tdsls411 | Sales Order Line Item Data
where
tdsls401.invn > 1470 and
tdsls401.item = tdsls411.item


And got weird result, records are multiplyed, like that:

Inv no Date Item code Amount
1470 15/09/2010 CODE1 123,00
1470 15/09/2010 CODE1 123,00
1470 15/09/2010 CODE1 123,00
1470 15/09/2010 CODE1 123,00
1470 15/09/2010 CODE2 234,00
1470 15/09/2010 CODE2 234,00
1470 15/09/2010 CODE2 234,00
1470 15/09/2010 CODE2 234,00
1471 15/09/2010 CODE3 345,00
1471 15/09/2010 CODE3 345,00
1471 15/09/2010 CODE3 345,00
1471 15/09/2010 CODE3 345,00
1472 16/09/2010 CODE1 222,00
1472 16/09/2010 CODE1 222,00
1472 16/09/2010 CODE1 222,00
1472 16/09/2010 CODE1 222,00

Anyone knows what's wrong with my query?

At final stage i want select invoices by date range and sum invoices for each item group.

hi,

Reason:
the item in table tdsls401 can be in many records in tdsls411 even with other sales order numbers! Therfore many more records are selected as you expect.

Solution:
Both tables have unique index on order (orno), position(pono) and sequence(sqnb) so you have to code as follows to find the correct record in tdsls411


select tdsls401.invn, | Invoice Number
tdsls401.invd, | Invoice Date
tdsls401.item, | Item
tdsls411.item, | Item
tdsls411.citg, | Item Group
tdsls401.damt | Amount
from tdsls401, | Sales Order Lines
tdsls411 | Sales Order Line Item Data
where tdsls401.invn > 1470 and
and tdsls411.orno = tdsls401.orno
and tdsls411.pono = tdsls401.pono
and tdsls411.sqnb = tdsls401.sqnb