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?

What DBMS is you are using? – Ankit Bajpai Jun 18 '14 at 07:51 — Ankit Bajpai, Jun 18 '14 at 07:51

score 0 · Accepted Answer · answered Jun 18 '14 at 07:50

0

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'

answered Jun 18 '14 at 07:50

Kiran Hegde

3,651
1
16
14

I found anther query, it retourns the same result ------------------------------------------------- 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' group by q.ID_Qt ORDER BY q.ID_Cat ,ctr.ID_Test DESC – white background Jun 18 '14 at 08:07
Ok, this is another way of writing the above query. instead of using join, where clause is used. regarding group by, as far as you dont have summary column selected, group by has the same effect as distinct. – Kiran Hegde Jun 18 '14 at 08:12
1

@white Background: It doesn't make much sense to select \* in combination with group by. Looks like you don't know exactly what you are doing. Kiran's query is okay, but joining the question table is superfluous; it doesn't add anything compared to simply getting the data from candidat_test_answer. – Thorsten Kettner Jun 18 '14 at 08:42
I want display all the question which the candidate passed by test (the table question contains informations that I need) ----------------------------------------------------------- id_question ||question || id_test || id_candidat || date | 1 || yyyyy? ||17 || 1 ||2014-06-01 | 2 || xxxxx? || 17 || 1 ||2014-06-01 – white background Jun 18 '14 at 08:56

score 0 · Answer 2 · answered Jun 18 '14 at 07:53

0

Why do you join with the question table? There is nothing in there you are intersted in, or is it? Simply select distinct rows from table candidat_test_reponse.

select distinct id_qt, id_test, id_cand, `date`
from candidat_test_reponse
where id_cand = 1 and `date` = '2014-06-01';

BTW: You should not use date for a column name. It's a reserved word in SQL.

answered Jun 18 '14 at 07:53

Thorsten Kettner

89,309
7
49
73

because I have to use the data table of the question like question example 1|bla bla bla bla? 2|blaa blaa blaa blaa? – white background Jun 18 '14 at 08:05
I don't understand. What does above query lack? It gets all the data you want. Exactly as requested. Or do you want to do something with the category column? It's neither in your output nor in your query. – Thorsten Kettner Jun 18 '14 at 08:46

score 0 · Answer 3 · answered Jun 18 '14 at 07:55

0

According to my understandability, your desired result can be achieved by using only 2nd Table.

Try somthing like this:-

SELECT id_question, id_test, min(id_candidat), date
FROM vcandidat_test_answer
GROUP BY id_question, id_test, date;

Hope this can help you.

answered Jun 18 '14 at 07:55

Ankit Bajpai

13,128
4
25
40

yes thanx , --------------------- 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' group by q.ID_Qt ORDER BY q.ID_Cat ,ctr.ID_Test DESC – white background Jun 18 '14 at 08:14

Removing duplicate rows from a SQL query

3 Answers3