I am working on using psql to crete dental charts. I have one table that has a value for each individual. It consists of an id for that person, and 32 additional columns (one for each tooth). These additional columns have numeric values.
In addition, there is a table for each tooth (obviously, 32 columns). Each of these tables has two columns. The first column has a list of numbers equivalent the numbers used in the columns of the first table. The second column has a filename for an image of that particular tooth in a different state (0 –> goodtooth.jpg, 1 –> filling.jpg, etc).
What I am trying to do is generate a query which will take the id from one set of records in the main table, and use the values in each tooth’s column to point to the correct image in each tooth’s table, thereby generating a dental chart. I’ve done inner joins to link records across tables, but that’s always been using the same id for each table. What do I use when I want to match multiple tables based on discrete values in multiple columns of one table?
I can’t get my head around where to start with this one. I’ve done select statements and gotten my output to display correctly, I’ve also worked with views when I’ve been working with many columns across tables. All of those, however, have been with one id that matched the other tables. I’m not sure where to begin where I have to use different ids for different tables.