Background: I am creating a member database for a homeowner's association. The database has a PEOPLE table, that contains contact information, a SITE table, that contains information about the property (e.g., street address), and a Junction table, PEOPLE_SITE_ROLE, supporting the many-to-many relationship between People and Site. This junction table contains role information, such as Owner, Renter, etc.
Question: I need to join the information from these three tables into a single view for reporting. It appears that Airtable Views are limited to a single table. Is that truly the case? What is needed to create this single view and support my reporting requirements?
Are these three tables linked through Reference fields? If you set the shared view to be expandable, all information would be in any of the three tables by clicking into any associated entry right? I'm not sure I'm understanding what you're seeing. Maybe creating a fourth table and just cut and paste everything into it? If this is a yearly report, and you like how it's organized as is, that might be the easiest.
Thanks for responding. I am fairly new to Airtable, but I have an extensive database experience using SQL. I'm trying to create a View in my Airtable database, that is the union of all columns in three tables, where they have linked relationships. In SQL terms, this is the same as an Inner Join.
Let me state my problem another way. Let's say there are two tables, Table A and Table B. Table A has columns a1, a2, a3, a4, and a5. Table B has columns b1, b2, b3, and b4. The primary keys for each table are a1 and b1, respectively. The two tables are linked through a relationship where a5=b1. A View of Table A shows columns a1, a2, a3, a4 and a5. A View of Table B shows columns b1, b2, b3 and b4.
Hope my examples help explain my conundrum. Thoughts?