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
Query help from 2 tables
Page 1 of 11 Replies - 917 Views - Last Post: 06 July 2012 - 03:28 PM
Replies To: Query help from 2 tables
#2
Re: Query help from 2 tables
Posted 06 July 2012 - 03:28 PM
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:
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.
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.
Page 1 of 1