SQL Statement to pull NULL values

[montanaheather]

Hello, All,

I am trying to figure out how to join multiple tables, pulling all null and non-null values for certain criteria. I need to pull answers from a single question, whether the answers were left blank or not, as well as the respondent type (i.e. "Constituency"). I have tried a combination of INNER JOIN, RIGHT JOIN, and LEFT JOIN, and this works fine when I am pulling information for two tables only. However, the respondent type is in another table that is not directly linked to the other two tables, but it is indirectly linked via a fourth table, Respondents. Below is a diagram of the relationships between all four tables. I want a SQL statement that pulls the Anwers (from the tbl_Question_List_Answer) for question ID 875, for project 11, as well as the Constituency from the tbl_Constituency. I want all answers, even if left blank.

Suggestions? Thank you!


Relationships.JPG

[jgomez]

Can you upload a sample?

[montanaheather]

Query1.xlsxI can't upload the database itself because it is used in our company. Here is what I have for SQL statements, and it appears to pull only non-null values.


SELECT PK_Question_ID, FK_List_Answer, FK_Constituency, PK_List_Answer_ID, Answer
FROM tbl_Feedback, tbl_Response, tbl_Question_List_Answers, tbl_Constituency
WHERE tbl_Feedback.FK_List_Answer = tbl_Question_List_Answers.PK_List_Answer_ID
AND tbl_Response.FK_Constituency=tbl_Constituency.PK_Constituency_ID
AND tbl_Feedback.PK_Response_ID=tbl_Response.PK_Response_ID
AND tbl_Feedback.PK_Question_ID=875

UNION ALL


SELECT PK_Question_ID, FK_List_Answer, FK_Constituency, PK_List_Answer_ID, Answer
FROM tbl_Feedback, tbl_Response, tbl_Question_List_Answers, tbl_Constituency
WHERE tbl_Feedback.FK_List_Answer = tbl_Question_List_Answers.PK_List_Answer_ID
AND tbl_Feedback.FK_List_Answer IS NULL
AND tbl_Feedback.PK_Question_ID=875;


Results are attached. I need all answers for FK_List_Answer, even if left blank. Right now I can only get the answers that have a value.

[Kyle123]

Is tbl_Feedback a Many-2-Many link table?

I'm having difficulty understanding your model

[montanaheather]

tbl_Feedback is one to many for tbl_Response and tbl_Question_List_Answers.
tbl_Constituency is one to many for tbl_Response.

Does that help?

Thank you!

[Kyle123]

I'm not really sure I understand, but how close does this get you?

SELECT pk_question_id, 
       fk_list_answer, 
       fk_constituency, 
       pk_list_answer_id, 
       answer 
FROM   tbl_question_list_answers 
       RIGHT JOIN tbl_feedback 
               ON tbl_question_list_answers.pk_list_answer_id = 
                  tbl_feedback.fk_list_answer 
       INNER JOIN tb_response 
               ON tbl_feedback.pk_response_id = tbl_response.pk_response_id 
       INNER JOIN tbl_constituency 
               ON tbl_response.pk_response_id = 
                  tbl_constituency.pk_constituency_id 
WHERE  tbl_feedback.pk_question_id = 875  

[montanaheather]

Thank you for the reply. This doesn't seem to work. I am using Access 2007 SQL. Does that matter?

[Kyle123]

I think you might need some brackets around the joins. I don't have access so I'm not sure on the syntax though. They'd go in the places you'd normally put them when joining talbes

[montanaheather]

Thanks. I will keep looking for help.

Have a good day.

[Kyle123]

Well does it work when you put the parentheses in?

Try:

SELECT pk_question_id, 
       fk_list_answer, 
       fk_constituency, 
       pk_list_answer_id, 
       answer 
FROM   ( tbl_question_list_answers 
         RIGHT JOIN tbl_feedback 
                 ON tbl_question_list_answers.pk_list_answer_id = 
                    tbl_feedback.fk_list_answer ) 
       INNER JOIN tb_response 
               ON tbl_feedback.pk_response_id = tbl_response.pk_response_id 
       INNER JOIN tbl_constituency 
               ON tbl_response.pk_response_id = 
                  tbl_constituency.pk_constituency_id 
WHERE  tbl_feedback.pk_question_id = 875  

Or

SELECT pk_question_id, 
       fk_list_answer, 
       fk_constituency, 
       pk_list_answer_id, 
       answer 
FROM  ( ( tbl_question_list_answers 
         RIGHT JOIN tbl_feedback 
                 ON tbl_question_list_answers.pk_list_answer_id = 
                    tbl_feedback.fk_list_answer ) 
       INNER JOIN tb_response 
               ON tbl_feedback.pk_response_id = tbl_response.pk_response_id )
       INNER JOIN tbl_constituency 
               ON tbl_response.pk_response_id = 
                  tbl_constituency.pk_constituency_id 
WHERE  tbl_feedback.pk_question_id = 875  

[montanaheather]

No, but it must be something I am doing wrong. I will keep searching for an answer, but if you think of anything else, I am all ears!

Thank you so much for your time!

[Kyle123]

Well what didn't work? Did it error? Did you not get the results you were after? It's difficult to help if you don't tell us what is happening

[montanaheather]

Hi, sorry about that! I get a syntax error:

Error.JPG


I have been reading a lot and I just can't figure it out. The joins make sense to me, but I can't get the correct syntax to get the output that I need.