I'm hoping to get some opinions on the best way to proceed. We are migrating our website from a Microsoft Environment to Drupal 7 and I appear to be having trouble wrapping my head around how to do a couple of things.
We have about 50 locations, and each location might have any number of workers. I have created 2 content types, Location & worker. What I want to do is to create a "view" (for lack of a better term) which will list all of my locations grouped by county. No problem. Now, when the user clicks on one of the locations, I want to show the data for the location (address, phone number, etc) but I also want the location content type to be able to pull a list of workers (from the worker content types) that are at that site.
I have a common field in each content type of location_number, so basically what I need to do is a SQL JOIN operation. I've looked into the Views module and it seems that using that I would have to create a separate view for each location (unless I'm missing something), when what I really want to do is create a more 'generic' view which I can plug into node template and have it auto-generate what I need. I don't want to have to manage 50+ views.
My first instinct is to say write some custom PHP code that gets the data I want. In SharePoint, I would just write a web part with a simple LINQ query right back to the 2 lists and plug it into the page. Is there something similar in to this in Drupal or is there an easier way to go about what I"m trying to do.
Any advice is appreciated.