Example: I want to build a data structure doctors (which will be a node of content type "doctor" not a user) who, if he becomes a user, will post to 3 other content types 1. articles, 2. case examples and 3. testimonials from patients. The Doctor will create all three content types or admin will create them and then change author to the appropriate doctor. (This is important because the creator is not always the author).
I know I can accomplish this with an entity reference field in every content type that references the doctor. However, I want the doctor's input form to be as simple as possible and they may forget or make a mistake in entering the entity reference field data.
It seems to me that I should be able to create a relationship in views (a join) based on the author field. Example: a "testimonials" view" that includes a picture of the doctor from his profile node not his user entity, that will link to his profile.
I know there are other ways to accomplish the solution (ie through user ID, which is available) but this only an example. There are other places in my site that I need to connect two different content types in a view based on an existing field (think city or state or color).
In database design this is easy. In a query, I can join two tables based on any field I want. How do I do the same thing in Drupal? This driving me crazy and there must be a simple answer.