5 Table select query

Hello,

I'm having trouble writing a select query with 5 tables becuase I don't want a huge amount of joins making a large overhead on the server.
The first 3 tables are all linked by one key and the other two tables are lookup tables for two codes that appear in the first table.

I want to select 5 columns from table 1, 3 columns from table 2 and 1 column from table 3. They are all linked by a key.

Is some kind of union involved, I couldn't get one working becuase of the differing number of columns I want from the tables.

Table 1
Table 2 All 3 linked by a key
Table 3


Table 4 Both of these relate to codes in table1
Table 5


Thanks in advance if anyone can give me any clues as I'm rather confused.

Thanks very much for that method r937, the first 3 tables im using have huge amounts of data in so im wondering if there is a way to do this with a smaller number of joins to try and cut down the processing on the server. maybe take the info from the the first table and the lookups in one query and then the other two tables in another query? i'm trying to cut down on processing overheads.

have you declared the proper indexes?

I've looked in the sysindexes table and their appears to be indexes for each of the table i need to use though i have no experience of indexes.

there is really no other way to obtain the data you want besides joining the tables

you should take the time to learn indexing and how to interpret the output of EXPLAIN statements

in the meantime, don't worry about the overhead

ok thanks for ur helpful advice as always