Removing duplicate rows from a SQL query

Question

I have a problem writing a SQL query. I have 2 tables: question and candidat_test_answer, formatted as follows.

question:

(id_question) (id_catgorie)

|  1             |1

|  2             |1

|  4             |1

candidat_test_answer:

id_question || id_answer  || id_test || id_candidat || date

|  1        ||  2         ||  17     ||     1       ||2014-06-01

|  1        ||  3         ||  17     ||     1       ||2014-06-01

|  2        ||  1         ||  17     ||     1       ||2014-06-01

|  2        ||  2         ||  17     ||     1       ||2014-06-01

|  1        ||  2         ||  17     ||     2       ||2014-06-01

I want to display records without duplication in the results.

id_question || id_test || id_candidat || date

|  1        ||  17     ||     1       ||2014-06-01

|  2        ||  17     ||     1       ||2014-06-01

Here is what I have tried so far:

SELECT * FROM  question q , candidat_test_reponse ctr  where q.ID_Qt=ctr.ID_Qt and ctr.ID_Test=17  and ID_cand=1  and date='2014-06-01'   
and q.ID_Qt NOT IN (SELECT q.ID_Qt FROM  question q , candidat_test_reponse ctr  where q.ID_Qt=ctr.ID_Qt and ctr.ID_Test=17  and ctr.ID_cand=1  and ctr.date='2014-06-01')
ORDER BY    q.ID_Cat  ,ctr.ID_Test DESC

However, it currently returns 0 results. What mistake have I made?

Answer 1

Try this query

select distinct question.id_question, candidat_test_answer.id_test,candidat_test_answer.id_candidat, date
from question join candidat_test_answer on question.id_question = candidat_test_answer.id_question
where ctr.ID_Test=17 and ID_cand=1 and date='2014-06-01'