Re: Annoying query

Hi Lionel (or should I say bonjour? :-) )

am I correct to understand that you want to get sum of Field2 from T1 for all the records that have a matching record in T2? If so, here is the way:

SELECT
    SUM(FIELD2)
FROM
    T1
WHERE
    FIELD1 IN (SELECT FIELD1 FROM T2) Does this answer your question? Feel free to ask!

Bruno

Bruno.Cossi_at_Consultant.com

"Lionel" <jlof_lavigne_at_yahoo.fr> wrote in message news:e4912eaa.0205160207.4a6b1839_at_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 - 11:31:36 CDT