On Mar 5, 7:32 am, mikejac...@gmail.com wrote:
> Hi, I have two tables which contain information which I need to use to
> populate a new table
>
> The first table (TableA) looks like the following
>
> ID | Age1 | Age2 | Age3
> =================
> 1 2 3 9
> 2 Null 4 2
> 3 6 Null 4
> 4 7 1 Null
>
> The second table (TableB) looks like the following
>
> ID | Age1 | Age2 | Age3
> =================
> 1 3 12 1
> 2 5 4 Null
> 3 4 Null 3
> 4 Null 1 2
>
> I need to create a third table from the two tables above which has the
> exact same format. Each column in the third resulting set is computed
> simply by the following maths (TableA.Age1 / TableB.Age1) * 100
>
> So for the above example the result set (TableC) would be:
>
> ID | Age1 | Age2 | Age3
> =================
> 1 66 25 900
>
> Is there a nice easy way to do this outside of using cursors and temp
> tables??
>
> Thanks
> Mike

On Mar 5, 3:57 pm, "Adi" <adic...@hotmail.com> wrote:
> On Mar 5, 7:32 am, mikejac...@gmail.com wrote:
>
>
>
>
>
> > Hi, I have two tables which contain information which I need to use to
> > populate a new table
>
> > The first table (TableA) looks like the following
>
> > ID | Age1 | Age2 | Age3
> > =================
> > 1 2 3 9
> > 2 Null 4 2
> > 3 6 Null 4
> > 4 7 1 Null
>
> > The second table (TableB) looks like the following
>
> > ID | Age1 | Age2 | Age3
> > =================
> > 1 3 12 1
> > 2 5 4 Null
> > 3 4 Null 3
> > 4 Null 1 2
>
> > I need to create a third table from the two tables above which has the
> > exact same format. Each column in the third resulting set is computed
> > simply by the following maths (TableA.Age1 / TableB.Age1) * 100
>
> > So for the above example the result set (TableC) would be:
>
> > ID | Age1 | Age2 | Age3
> > =================
> > 1 66 25 900
>
> > Is there a nice easy way to do this outside of using cursors and temp
> > tables??
>
> > Thanks
> > Mike
>
> There is no need to do cursor or temporary table. You do an insert
> select statement that does it:
>
> INSERT INTO TableC (id, age1, age2, age3)
> select t1.id, t1.age1/t2.age1*100, t1.age2/t2.age2*100,
> t1.age3/t2.age3*100
> FROM TableA t1 INNER JOIN TableB t2 ON t1.id = t2.id
>
> By the way I admit that I don't know the whole structure of the
> database, but whenever there is a table with columns names that repeat
> few times (each time with a different number) it baekes the Normal
> Form (A table should not contain repeating columns) and most of the
> time there is a better data structure that can be used.
>
> Adi- Hide quoted text -
>
> - Show quoted text -