Help with a SELECT query

shaunie123

Hi,

How can I SELECT the name of a column from a table where all the values in that column are null?

Thanks

steadyguy

Uh ... why would you set up a column, knowing that it would never have values in it? Also, this implies that you don't know the name of the column ... which is sort of a requirement ... BUT in a case such you describe, you can use DESCRIBE tableName; , or SHOW COLUMNS FROM tableName; .

This will give you a result set with columns Field, Type, Null, Key, Default, and Extra.

Iterate over the field column to get a list of all the fields in the table.

shaunie123

Thanks for your reply,

Basically I have 2 tables:

Data
Data_Field_1
Data_Field_2
...
Data_Field_n

Fields
Field_ID
Field_Name
Data_Field

The puropse of this is so users can create projects and collect data for each project and create their own field names. The data will be stored in Data and the Fields will hold the Field names to make entering data easier. Column Data_Field in table Fields holds the corresptonding column name in table Data of the data stored for that Field.

So what i would like to be able to do is select column(s) from data where Field.Data_Field = Data.Column_Name. Is this possible?

Thanks for your help

steadyguy

Sure ... You've even done half the work yourself.
SELECT column[, column[, column[...]]]
FROM Field
WHERE Field.Data_Field = Data.Column_Name;

shaunie123

But seeing as i dont know the column name what do i replace column with?

Field.Data_Field = Data.Column_Name is used to find the column name!

steadyguy

You'll first need to use the DESCRIBE tableName query I mentioned earlier, to get the field names, then you can insert column name as appropriate into the SELECT query, run it, and get your results.