I have a master detail set of tables with a one-to-many relationship. Let's call the tables Projects (master) and Markets (details).
I need to allow users to query the database and be able to select projects by markets. In other words, users should be able to "say": "List all projects where Market is Architecture and Construction"
The query is to be created dynamically (this is not the problem here) and there are lots of projects and lots of markets.
Can anyone guide me on how to create the query (conceptually).
Below is a simplified version of the two tables. Now, I would like to query: "list all projects that is offices and hospitality"; with this sample data I should get project 001 only
PrjId PrjName... etc
PrjId MrktId Mrkt1 Mrkt2 Mrkt3 ... etc
001 001 offices single-family NULL
001 002 hospitality NULL NULL
001 003 retail NULL Something
002 004 offices NULL NULL
003 005 hospitality NULL NULL