Get Column Names from Column Values

[franciscog354]

Hi,

I'm trying to build a questionnaire where depending on the first question (SELL or BUY) different questions will be asked. To make it simple (now I think I was wrong) I created two tables:

Table1
RowId | Q00 | Q01 | Q02 | Q03 | ...
1 SELL YES KY HOUSE
2 BUY LOFT 90000$ 120

Table2
QId | SELL | BUY
Q01 Only owner? Type of good
Q02 Address? Budget
Q03 Type of good Square meters
...



In this way I thought I could query both tables replacing the column names in Table1 (Q01, Q02,...) with the values from Table2 ([Only owner?], [Address?],...) assuming in this case Q00 was 'SELL'.

Is this possible? Did googled quite much but not luck at all...

Thanks in advance,
Fran

 

[gmmastros]

I think your table layout (database structure) is flawed. Are you able/willing to change it?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[franciscog354]

Thanks for the response. Yes, of course, I would be able to change the structure. It was just a thought but as I went on with it I kind of realized it was not the best way... guess was right :|

Any suggestions?

 

[gmmastros]

I would suggest something along these lines...

[tt][blue]
Question
Id (int) Description (VarChar)
-------- ---------------------
1 Hungry?
2 Thirsty?
3 Breakfast?
4 What type of food?
5 Belgian Waffles?
6 Bacon and Eggs?

Answer
Id (int) Description (VarChar)
-------- ---------------------
1 Yes
2 No
3 Sweet
4 Savory

QuestionAnswer
QuestionId (int) AnswerId (Int) NextQuestionId (int)
---------------- -------------- --------------------
1 1 3
1 2 NULL
3 1 4
3 2 NULL
4 3 5
4 4 6
5 1 NULL
5 2 NULL
6 1 NULL
6 2 NULL
[/blue][/tt]

There are some advantages to this table structure, but let's take a look at it before we discuss advantages. Notice that the first question is "Hungry?" Now, look at the QuestionAnswer table for QuestionId = 1. We have 2 rows, one with answerid = 1 and another with answerid = 2. Now look at the Answer table. Answerid = 1 is Yes, and AnswerId = 2 is No. If the survey answer for question 1 is 2 (no), the QuestionAnswer table shows the next question as NULL. A NULL would represent no more questions. If the user answers Yes, the next question would by QuestionId 3 (Breakfast?)

Can you see how this structure works?

Now, let's talk about the advantages... If you decide (later) to add more questions, you don't need to modify any of your tables (structure), all you need to do is add more data to the tables. Also, with a structure like this, we can have more than 2 responses for each question.

You could even add a bit column to the Question table for "AllowMultipleResponses".

[tt]
Answer
Id Description
----- -------------
200 Eggs
201 Bacon
202 Sausage
203 Waffles
204 Toast
[/tt]

Of course, each of those questions could lead to different questions. Like.... 200 could lead to (Eggs over easy, scrambled, poached) and 204 could lead to (Grape Jelly, Strawberry Jelly, Orange Marmalade).

Make Sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[franciscog354]

Hi gmmastros,

thank you very much. Never thought of it this way. It really makes a lots of sense.