sql select with count condition

Question

I have 2 tables in my DB, first table called "questions" and hold questions with id, second table called "answers" and hold answers for the questions (as multiple choices).

how to select questions that have less than 4 answers?

questions table:

id     question
1      what is ...?
2      how many ...?
3      Is ....?

answers table

id     question_id     answer
1          1             54
2          1             11
3          1             22
4          2            England
5          1              5
6          2            Turkey

how to select questions that have answers less than 4?

thanks,

ah_hau · Accepted Answer · 2014-08-18 05:25:57Z

3

select questions.id, questions.question from questions 
inner join answers on questions.id = answers.question_id
group by questions.id, questions.question having count(questions.id) <4

here you go.

edited Aug 18, 2014 at 5:25

answered Aug 18, 2014 at 3:42

ah_hau

7684 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ted Over a year ago

I want to return questions (text)

Farrokh Over a year ago

your join is not perfect because none of second table columns selected, select question.id, questions.question from questions means that the join is expected

ah_hau Over a year ago

@Farrokh, what's d problem for not having column selected on 2nd column? joining it just to get the count for list of answers. subquery normally will take longer time to execute when the data is huge which inner join is normally use the primary key which result faster. But anyway there was missing some s on my questions, fixed it

MarmiK · Accepted Answer · 2014-08-18 04:25:46Z

0

use this :

SELECT * 
FROM questions 
WHERE id in
    (
    SELECT question_id 
    from answers 
    group by question_id 
    having count(question_id) <4
    )

edited Aug 18, 2014 at 4:25

MarmiK

5,8156 gold badges47 silver badges51 bronze badges

answered Aug 18, 2014 at 3:46

Farrokh

1,1771 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

sql select with count condition

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related