hi, I was wondering if anyone co

[IanNav]

hi,

I was wondering if anyone could tell me how select a load pf data with joins which are linked one-2-many???

i'll probably explain better through my example

Table 1
========
Date(key) Ords Value
=========================
01/08/2001 8 550
02/08/2001 4 1070
03/08/2001 3 1150
04/08/2001 7 60
05/08/2001 1 480
06/08/2001 6 500



Table 2
========
Date(Key) Code(Key) Ords Value
====================================
01/08/2001 01 3 500
01/08/2001 03 3 100
02/08/2001 01 4 330
02/08/2001 02 5 578
02/08/2001 03 6 1245
03/08/2001 01 5 1199
03/08/2001 03 8 1999
03/08/2001 04 9 200
04/08/2001 05 3 500
04/08/2001 06 4 349
05/08/2001 01 8 564
06/08/2001 01 2 2356
06/08/2001 04 2 4506

what select do i use to get a recordset (below)

basically it needs to add the 'ords' & 'value' up in table 2 by date, then link it to table 1

Date [tab1].ords [tab1].val [tab2].ords [tab2].value
=========================================================
01/08/2001 8 550 6 600
02/08/2001 4 1070 15 2153
03/08/2001 3 1150 22 3398
04/08/2001 7 60 7 849
05/08/2001 1 480 8 564
06/08/2001 6 500 4 6862

I'm sure this is easy to do, but i'm not an SQL expert.

Thanks

Ian

 

[mookie]

Try this

SELECT Table1.Date, Table1.Ords, Table1.Value, Sum(Table2.Ords), Sum(Table2.Value)
FROM Table1 INNER JOIN Table2 on Table1.Date = Table2.Date
GROUP BY Table1.Date, Table1.Ords, Table1.Value
ORDER BY Table1.Date

 

[IanNav]

Mookie,

I've tried it in SQL Query Analyzer, it does'nt return any errors, but does'nt return any data either.

Do you reckon you could have a look again?

Thanks, i really apprieate it.

Ian

 

[IanNav]

Mookie,

I've tried it in SQL Query Analyzer (using the example i posted - which is not exact to the one i need...)

i just posted something small up for convienace.

Infact i have (below) this in table one. and i still want to produce (see bottom) grouped by date results

Table 1
========
Ref(Key) Date Ords Value
===================================
1 01/08/2001 8 550
2 01/08/2001 8 550
3 02/08/2001 4 1070
4 02/08/2001 4 1070
5 03/08/2001 3 1150
6 03/08/2001 3 1150
7 03/08/2001 3 1150
8 04/08/2001 7 60
9 05/08/2001 1 480
10 06/08/2001 6 500


RESULTS
=======

Date [tab1].ords [tab1].val [tab2].ords [tab2].value
=========================================================
01/08/2001 16 1100 6 600
02/08/2001 8 2140 15 2153
03/08/2001 9 3450 22 3398
04/08/2001 7 60 7 849
05/08/2001 1 480 8 564
06/08/2001 6 500 4 6862


What do you reckon?

Thanks loads.

Ian

 

[mookie]

Because date is not a key field you will need to use sub-queries

SELECT T1.Date, T1.SumOrds, T1.SumValue, T2.SumOrds, T2.SumValue
FROM
(Select Date, Sum(Ords) as SumOrds, Sum(Value) as SumValue FROM Table1 GROUP BY Date) as T1 INNER JOIN
(Select Date, Sum(Ords) as SumOrds, Sum(Value) as SumValue FROM Table2 GROUP BY Date) as T2 ON T1.Date = T2.Date