0

I got two following tables

category:(one to many-----question) 

id name  parentId
1  test1  0
2  test2  1
3  test3  1
4  test4  3 .....

like a tree 

test1
  test2
  test3
    test4

question: 

id   title      category_id
1   question1    1
2   ....         1
3   ....         2

my question is : if i search category id = 1 ,there will be print total questions:

count(question.id)
     3

How to build Select query to do that? is that possible?

Thank you for your valuable help.

Improve this question
6
  • Why do You think it should be Recursive SELECT ? Commented Nov 5, 2013 at 8:14
  • @oleg why not? give me some tips.. Commented Nov 5, 2013 at 8:22
  • simple select with COUNT and WHERE as @Legionar has proposed or COUNT with GROUP BY if You need more questions count not for category_id=1 only Commented Nov 5, 2013 at 8:27
  • no,You do not understand my intentions,the category table like a tree.. Commented Nov 5, 2013 at 8:54
  • 1
    Unlike almost all other DBMS MySQL does not support recursive queries. You will need to write a stored procedure for that (or upgrade e.g. to Postgres or Firebird) Commented Nov 5, 2013 at 9:00

3 Answers 3

Reset to default
2

This will return count of questions for your selected category, here in example category_id = 1:

mysqli_query('SELECT COUNT(id) FROM question WHERE category_id = 1');

Updated: so, if you want to count it also for subcategories, the simpliest way will be to have "path" in your category table, where will be all IDs (self ID and ID-s of all parents), and you can separate it with ~ (its important to have ~ also at the beginning and end of path; path can be VARCHAR(255), but if you want have really deep tree, you can use TEXT.

id name   parentId  path
1  test1  0         ~1~
2  test2  1         ~1~2~
3  test3  1         ~1~3~
4  test4  3         ~1~3~4~

Hope, its clear enough, how you will update your table category to have there also column path.

And the select then will be:

mysqli_query('
  SELECT COUNT(id)
  FROM question
  WHERE category_id IN (
    SELECT id
    FROM category
    WHERE path LIKE "%~'.$category_id.'~%"
  )
');

$category_id will be ID of category, for which you want to count questions (also for subcategories).

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

7 Comments

how to get the questions belong category's subcategories?
Then you should use recursive query; sorry, it was not clear enough in your question.
It is my fault, i did not describe the problem clearly. But also thank you for your answer.
I can help, how deep will be your category tree? It will be exact number, or you dont know how deep it will be?
nice catch,Thank you very much, i will try to follow your idea.
|
2

This is a very simple query:

SELECT COUNT(*) FROM question WHERE category_id='1';

But you probably want a join query like this:

SELECT COUNT(*) FROM question INNER JOIN category 
ON (question.category_id = category.id) WHERE category.name='test1';

That will give you the option of searching for category names.

(BTW, did you google this?)

Edit: took me a cup of coffee, but indeed a LEFT JOIN does not make sense if the WHERE is on the joined table. INNER JOIN does.

Improve this answer

5 Comments

Thank you for your answer, hum, Perhaps I did not describe the problem clearly. a category may be has subcategories, and i want get all the questions even that question belongs subcategories
There really isn't much point combining a LEFT [OUTER] JOIN with a WHERE condition on the joined table - unless that WHERE condition is IS NULL
Leo, my guess was that you want to get the questions that belong to a category. My second query allows searching for category name and get the number of questions.
Strawberry, enlighten me: how would you search for questions based on category name?
See a_horse_with_no_name's (and my) comment above. Your answer still misses the point. ;-)
-1

Try like this,

 SELECT COUNT(id) FROM question WHERE category_id IN (SELECT id FROM question WHERE category_id='1')
Improve this answer

2 Comments

This does NOT do anything that makes sense. The subquery is not necessary if you already know the category_id.
That select is completly wrong, you are mixing question_id with category_id. Just read it once again, and you will see that its wrong...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.