Page 1 of 1

Joining

Posted: Tue Mar 30, 2004 8:07 am
by Shendemiar
I have two tables like

Table A
Index1, Index2, Location

Table B
Index1, Index2, Result_type, Result
(So each row represents one result_type & result pair)

And i'd like to have a table like
Index1, Index2, Location, Result, Result, Result...
(Where all results are inserted in predefined resulttype columns like Tempereture, Oxygen etc...)

Posted: Tue Mar 30, 2004 9:33 am
by JAM
Needs rewriting, but the basics are there. The problem with joining tables that have the same fieldsnames on both, are that the query itself will not know what (in this case) index1 is witch etc.

But by naming them as in the example, you can bypass this.

Code: Select all

select 
 tablea.index1 as 'a-index1',
 tablea.index2 as 'a-index2',
 tablea.location,
 tableb.index1 as 'b-index1',
 tableb.index2 as 'b-index2',
 tableb.result_type as 'restype',
 tableb.result
from
 tablea
 inner join tableb on tablea.index1 = tableb.index1
Hope it gave you enough ideas to work with.

Posted: Tue Mar 30, 2004 12:55 pm
by Shendemiar
I did it with multipple updates.... 49 in fact. Not very fancy but i got it.