Finding column name in table using SQL

Question

I am currently using Advantage database. I have a table with hundreds of headers. How do I use query to find a specific column name?

Example - I am looking for a column header named "Marital Status", and the only details I have are the values "Married" & "Single". Is there a query I can use to find out the column name just by using its values?

Answer 1

From the description, there is probably a lookup reference table (or more) where there is some keyID and a corresponding value, and possibly a column for what the lookup is used for. Then, you would probably query from THAT table, find the ID, then in your employee (or person) table, there is probably some "ID" column such as MaritalStatus. That is where your missing piece probably lies. For example

SomeLookupTable
LookupID   UsedFor        UserValue
1          MaritalStatus  Married
2          MaritalStatus  Single
3          MaritalStatus  Divorced
etc...
8          Gender         Male
9          Gender         Female
...
16         SomeOtherUse   Some Value
17         SomeOtherUse   Continue.


Person (or Employee table)
PersonID  GenderID  MaritalStatus
1         8         1
2         9         3

So, in the above person table, the marital status would be joined to the lookup for the respective Married, Single, Divorced, Widowed, etc value, so you would never see the words directly in the person table.