Help with query

I have two tables. Person and Answers. The Person table has the column person_id. The Answers table contains person_q_id, person_id, question_id, value, answer_date, questionnaire_id. What I need to find out, are person_id's that don't have an entry for a certain question_id in the Answers table. So for example, I want to find participants that haven't answered question id Q_1. I have the following:

Person
person_id
101
102
103

Answers
person_q_id   person_id   question_id   value   answer_date   questionnaire_id
1                       101              Q_1                 Yes      20150601        I-1
2                       101              Q_2                 Yes      20150601        I-1
3                       103              Q_2                 No       20150508        I-2
4                       103              Q_3                 Yes      20150508        I-3

So, the query would return 102 and 103. Any ideas?
Horalia RodriguezIT DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Do you have a question table?
0
Horalia RodriguezIT DeveloperAuthor Commented:
No, just answers.
0
johnsoneSenior Oracle DBACommented:
Based on what you specifically asked for this is what I would do:
SELECT person_id 
FROM   person 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   answers 
                   WHERE  person.person_id = answers.person_id 
                          AND question_id = 'Q_1') 

Open in new window

However, I believe there is more to the question than what you are asking.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
Or if you would prefer using the outer join syntax...
SELECT person_id 
FROM   person p 
       left outer join answers a 
                    ON person.person_id = answers.person_id 
WHERE  a.question_id = 'Q_1' 
       AND a.person_id IS NULL  

Open in new window

This way might be more conducive to your ultimate goal, whatever that is.
0
Brian CroweDatabase AdministratorCommented:
SELECT P.person_id, Q.question_id
FROM Person AS P
CROSS JOIN (SELECT DISTINCT question_id FROM Answers) AS Q
LEFT OUTER JOIN Answers AS A
      ON P.person_id = A.person_id
      AND Q.question_id = Q.question_id
WHERE A.person_q_id IS NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
select person_id from person
except
select person_id from answers
where question_id = Q_1;
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
horalia, do you still need help with this question?
0
Horalia RodriguezIT DeveloperAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.