Database lookup for each value of another database lookup

Question

I am not sure how to exactly word this as I have just started SQL and have just finished going over simple joins.

Ill start with my tables first:

tblQuestions

| Survey ID | User ID | Entry Date | Question 1 | Question 2 | Question 3 | Question 4 |
|     1     |   305   |    date    |      0     |      1     |     2      |      3     |

tblValues

|  VID  | TextValue |
|   0   |   Never   |
|   1   |   Rarely  |
|   2   | Sometimes |
|   3   |   Often   |

I want an SQL query that outputs the TextValues of each questions VID. I can't store the text values directly because the data will be exported for charts. Also I from what I understand repetitive data is bad table design and rule for when to normalize tables.

Is this possible through a single query?

EDIT: I am using MS Access. Desired output:

| Survey ID | User ID | Entry Date | Question 1 | Question 2 | Question 3 | Question 4 |
|     1     |   305   |    date    |    Never   |   Rarely   |  Sometimes |    Often   |

I'd have tblQuestions as (SurveyID, QuestionNo, VID). (And don't forget foreign keys!) — jarlh, Jan 29 '16 at 08:08
So a survey always consists of four questions (the text of which is in another table or not in the database at all)? And all questions can be answered with one of the four tblValues? And your desired output for above data is 1 - 'Never' - 'Rarely' - 'Sometimes' - 'Often'? — Thorsten Kettner, Jan 29 '16 at 08:17
jarlh - How would I get the TextValues? @ThorstenKettner - It has other fields as well but those are the ones that I want populated with tblValues. And yes, that is the desired output. — Chris, Jan 29 '16 at 08:20
Depending on engine you can use CASE statement insted of lookup table. — Klaudiusz bryjamus, Jan 29 '16 at 08:21
By the way: With SQL questions you should always: 1. state the DBMS you are using because SQL exists in different dialects, 2. show the desired output, 3. show the query you have got so far, so we see where exactly you are stuck. — Thorsten Kettner, Jan 29 '16 at 08:21
@ThorstenKettner Updated question with DBMS and desired output. I am nowhere with my own query. The standard join queries are giving me outputs in the complete opposite direction. — Chris, Jan 29 '16 at 08:28
Hi Chris, your edit brings up that you are working with MS-Access. You'll need to adapt my answer which is built for SQL Server, but you'll get the idea... — Shnugo, Jan 29 '16 at 08:30
@Shnugo Sorry you had already typed it all out before you saw the edit. My mistake for not mentioning it earlier. I didn't realize it was relevant. — Chris, Jan 29 '16 at 08:33
@Chris All questions with "sql" should state the RDBMS (vendor and version), but in this case you should be able to get the idea and build the structures with Access easily. — Shnugo, Jan 29 '16 at 08:36

Thorsten Kettner · Accepted Answer · 2016-01-29T10:43:01.603

2

You want to join one survey record with four answer records, so you must query the answer table four times. Use table aliases in order to distinguish the four records:

select 
  q.survey_id, q.user_id, q.entry_date,
  v1.textvalue, v2.textvalue, v3.textvalue, v4.textvalue
from tblQuestions q
join tblValues v1 on v1.vid = q.question1
join tblValues v2 on v2.vid = q.question2
join tblValues v3 on v3.vid = q.question3
join tblValues v4 on v4.vid = q.question4;

UPDATE: Access has some additional join requirements, as Chris has pointed out. So above standard SQL query has to be modified thus:

select 
  q.survey_id, q.user_id, q.entry_date,
  v1.textvalue, v2.textvalue, v3.textvalue, v4.textvalue
from (((tblQuestions q
inner join tblValues as v1 on v1.vid = q.question1)
inner join tblValues as v2 on v2.vid = q.question2)
inner join tblValues as v3 on v3.vid = q.question3)
inner join tblValues as v4 on v4.vid = q.question4;

edited Jan 29 '16 at 10:43

answered Jan 29 '16 at 08:30

Thorsten Kettner

89,309
7
49
73

BTW: As you see from jarlh's comment and Shnugo's answer, it is recommended to have another database design where you have a table with one record per answer. This would not only make querying the data much easier, but also enable you to have surveys with three, five or hundred questions. – Thorsten Kettner Jan 29 '16 at 08:37
What if I know for a fact that those 4 questions are the only ones that will require responses like that? Would you still recommend changing the structures of the tables? – Chris Jan 29 '16 at 08:41
Are you saying that you only ever have surveys with exactly four questions? Or that all surveys have the same four questions plus other questions? Or something else? – Thorsten Kettner Jan 29 '16 at 08:46
I am saying I may add more questions to the survey later but they won't be "Never, Rarely, Sometimes, Often" questions. – Chris Jan 29 '16 at 08:48
*The* survey? So there is only one survey and you don't intend to have more some time? And the other questions are all multiple choice, too? Or free text questions? – Thorsten Kettner Jan 29 '16 at 08:50
Yes, there is only ONE survey which may get more questions added to it later but those ones can be stored as text. The reason I needed INT values for Question 1 to 4 was to build charts off of them. Also I am looking up table aliases and shouldn't `from tblQuestions q` be `from tblQuestions as q`? – Chris Jan 29 '16 at 08:52
In this case where you don't need any flexibility, you can stay with your design. As to `AS`: I don't know if this is optional or mandatory for table aliases in Microsoft Access. – Thorsten Kettner Jan 29 '16 at 09:02
With a little bit of tweaking this solution has worked perfectly. 3 things need to be changed for this to work in access. The first is aliases require `AS`. Secondly, Access syntax requires `INNER JOIN` instead of just `JOIN`. Lastly, access requires parenthesis in the `FROM` clause for multiple inner joins, see here: [Multiple INNER JOIN SQL ACCESS](http://stackoverflow.com/a/20929533). – Chris Jan 29 '16 at 09:42
Ah, alright. Sorry you had to work this out. Now that you mention it, yes, MsAccess has a strange join syntax, different from other DBMS. – Thorsten Kettner Jan 29 '16 at 09:56

score 0 · Answer 2 · answered Jan 29 '16 at 08:28

Just as a start:

CREATE TABLE Survey(ID INT NOT NULL IDENTITY CONSTRAINT PK_Survey PRIMARY KEY
                   ,BeginOfSurvey DATETIME NOT NULL);
CREATE TABLE Value(ID INT NOT NULL IDENTITY CONSTRAINT PK_Value PRIMARY KEY
                  ,Caption VARCHAR(100) NOT NULL);
CREATE TABLE QuestionText(ID INT NOT NULL IDENTITY CONSTRAINT PK_QuestionText PRIMARY KEY
                     ,Caption VARCHAR(100) NOT NULL);
CREATE TABLE Question(ID INT NOT NULL IDENTITY CONSTRAINT PK_Question PRIMARY KEY
                     ,SurveyID INT NOT NULL CONSTRAINT FK_Question_SurveyID FOREIGN KEY REFERENCES Survey(ID)
                     ,QuestionTextID INT NOT NULL CONSTRAINT FK_Question_QuestionTextID FOREIGN KEY REFERENCES QuestionText(ID)
                     ,ValueID INT NOT NULL CONSTRAINT FK_Question_ValueID FOREIGN KEY REFERENCES Value(ID));
INSERT INTO Value VALUES
 ('Never')
,('Rarely')
,('Sometimes')
,('Often');

INSERT INTO QuestionText VALUES
 ('Do you smoke?') 
,('Do you drink  alcohol?'); 

INSERT INTO Survey VALUES(GETDATE());

INSERT INTO Question VALUES
 (1,1,2) 
,(1,2,3);

SELECT *
FROM Survey AS s
INNER JOIN Question AS q ON s.ID=q.SurveyID
INNER JOIN QuestionText AS qt ON qt.ID=q.QuestionTextID
INNER JOIN Value AS v ON v.ID=q.ValueID

Database lookup for each value of another database lookup

2 Answers2

Linked