Question:
I have a query that retrieves a field which is a foreign key in two tables. The tables are organization_name and person_name. Each instance of the foreign key is in one of the tables but not both.
How do I use the field to retrieve either the organization_name or the person_name, depending on which table matches the value of the particular instance of the foreign key? The fields I want to retrieve are called org_disp_name or pers_disp_name. I know I have to create another field_name and somehow fetch either org_disp_name or pers_disp_name into it, but nothing I’ve tried has worked.
Answer:
I’m a little puzzled by organization_name and person_name being table names; they sound like attributes to me. I’m also not sure if there is some ambiguity over which name got used or not.
The problem goes away if one table is structured like this:
FKColumnName, FKTableName, FKValue
This lets you map back to the table contributing the foreign key, know the column name to use, and the value to compare. I am assuming that the FKValue will yield the text representation you desire, one or the other of organization_name or person_name.
