display of records from multiple tables

[fm_notaware]

i need some help related to display of records.
I have 4 tables storing record of a property
table 1 stores the location
table 2 stores area/ price of each unit in that property (there can be more than 1 unit for same property)
table 3 stores all images of the property
table 4 stores all doc/pdf files of the property

now when i display can i open all four tables with a single recordset and if so how to display each unit in a loop , each photo in a loop, each file in a loop.
or can anyone suggest something better.
thanking in advance

[carguy84]

I would just make multiple calls. If your DB is indexed properly, those selects will be very fast and with very low overhead.

[fm_notaware]

Thank you carguy84 for the inputs.....
i have not used indexing as yet because I have common tables like photo which is been used by other property type table
eg.
1 residential property table
2 commercial property table
both are linked to photo table

now can i set primary key on both residential property table and commercial property table on property_ID and link it to photo table having a common foreign key on property_ID . i am sorry i am not to sure if i can do this.... please guide

[LifeinAsia]

You can have multiple indexes on a table.

[wingnut]

Table 1 has Property Id field that is unique, indexed or not. This field value is used in the child tables (Tables 2,3 and 4) to associate it with the property in table one. You then use a JOIN statement to list all data in one recordset though displaying the information would be complex.
It would be better to do as carguy84 says and make multiple calls within one recordset loop.

[fm_notaware]

yes wingnut i have already made multiple calls within one recordset loop but now i'm thinking about indexing... how can i assign a foreign on a table which is used by two parent tables