Annoying query

From: Lionel <jlof_lavigne_at_yahoo.fr>
Date: 16 May 2002 03:07:20 -0700
Message-ID: <e4912eaa.0205160207.4a6b1839@posting.google.com>

Hello
I am quite a new user of SQL language and I face the following problem with a query.
the query is as follows:
select
 T1.field1, T1.field2, T2.field3, T2.field4 from
 T1,
 T2
where
 T1.field1=T2.field1

Apparently, quite a basic query.
The trouble is that, the table T2 can have several lines with fitting the condition.
Suppose the tables T1 and T2 contain the following information

T1                  T2
field1, field2      field1, field3, field4
xxxx, 3             xxxx, a, aaaa
yyyy, 5             yyyy, b, ttt
zzzz, 6             yyyy, c, ttt
                    zzzz, d, ddd

Hence the results of such query on these two tables would be T1.field1, T1.field2, T2.field3, T2.field4

xxxx, 3, a, aaa
yyyy, 5, b, ttt
yyyy, 5, c, ttt
zzzz, 6, d, ddd

Consequently, when I am trying to use the results of that query to make a sum on field2 the result is 19 when I expected 14 as the value is added as many times as there are lines returned. One simple solution would be to ignore field3 and field4, unfortunately, I need them.
I have tried to use DISTINCT in the query, to make a subquery to limit the results, group by and eveything else that I could think of, but nothing worked.
I hope that my description of the problem is understandable

Could anyone help me ?
Thanks
Lionel Received on Thu May 16 2002 - 05:07:20 CDT