[polska03]

Hello
I have two tables, data and metadata. Metadata has these columns project_id(id of project, references Data's project_id), field_name(variable that that describes value, references Data's field_name) , element_label (label for field). Data has project_id (references metadata's project_id) , field_name (references metadata's field_name), value (the actual data value). I want it to output 3 things.

ex. project_id=1, field_name="Sex of person", element_label="SEX", value="MALE"
project_id=1, field_name="Age of person", element_label="AGE", value="20"
....(

I want the element_label to be the "columns" in the finished query plus have a final column that shows the project_id (don't need field_name, but thought it might be used for referencing tables). And under these element_label columns" to have the corresponding values. I am completely stuck how to do this, please help!

I tried things such as SELECT element_label FROM metadata WHERE project_id=1 to get the columns names and put them in a table in html,then to get the values (SELECT values FROM data WHERE element_label=(used php's code to refrence table columns) to go under each column but I couldn't get to work. If there is a way to just get all this information in one single query that would be great,

Cheers

[e_i_pi]

It sounds like you have two tables that have a one-to-one relationship, via a composite key. So, you should be able to use a JOIN to get all the information you need:

SELECT
  Data.project_id,
  -- Add in any other columns you want here
FROM Metadata
INNER JOIN Data
  ON Data.project_id = Metadata.project_id
  AND Data.field_name = Metadata.field_name

The one issue that comes to mind is that you may have rows in one table that don't relate to the other table. In that case, you'd need to determine if one of the tables is the authority (or master table), then get that table to LEFT OUTER JOIN against the slave table. If neither table is the master, then you'd have to use FULL JOIN, and wrap some logic around the SELECTion of attributes, so tat you preference values over nulls.