All geek questions in one place

Mysql is a complex connection, anyone?

I need to write such a request, please, any help?

select id, parent, faq, (select count(*) from faq_table where parent = (select id from faq_questions) group by id) as reply from faq_table

This table stores questions and answers (for example, a question about frequently asked questions), and the answers get the value of the question identifier in the parent column. Ideally, I would also like to add a second column called par, where all questions get a value of 1.

how

 id | parent | faq 19 | 0 | name a president of the US 20 | 19 | Bush 21 | 19 | Obama 22 | 0 | Can Canada win the WC match against the Lankan today 23 | 22 | Yes because Cheema is going to make a double today

In the resulting table from this query, I should get:

 id | parent | faq | reply | par 19 | 0 | name a president of the US | 2 | 1 20 | 19 | Bush | 0 | 0 21 | 19 | Obama | 0 | 0 22 | 0 | Can Canada win the WC match against the Lankan today | 1 | 1 23 | 22 | Yes because Cheema is going to make a double today | 0 | 0
+4
source share
4 answers

This will only work for one level of the hierarchy:

 SELECT t.id, t.parent, t.faq, IFNULL(g.Cnt, 0) as reply, g.Cnt IS NOT NULL AS par FROM faq_table t LEFT JOIN ( SELECT parent, COUNT(*) Cnt FROM faq_table WHERE parent > 0 GROUP BY parent ) g ON t.id = g.parent

Otherwise, you can use the subquery:

 SELECT t.id, t.parent, t.faq, (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) as reply, (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) > 0 As par FROM faq_table t
+2
source

Using your request and this fact

  • The answers
  • get the question id value in the parent column

Demand

a second column named par, where all questions get a value of 1.

becomes trivial and seems to confirm your data.

 select id, parent, faq, (select count(*) from faq_table where parent = (select id from faq_questions) group by id) as reply, if(parent=0,1,0) as par from faq_table

Basically, when parent = 0, it is the parent. But since the comments indicate that there is only one table, the above should be pseudo - the correct query should be

 select id, parent, faq, (select count(*) from faq_table t where t.parent = faq_table.id) as reply, case when parent = 0 then 1 else 0 end as par from faq_table
+1
source

This version also works;

  SELECT q.id, q.parent, q.faq, count (a.id) AS reply, IF (ISNULL (a.id), 0,1) as par
 FROM faq_table q
 LEFT JOIN faq_table a
   ON q.id = a.parent
   AND a.id IS NOT NULL
 GROUP BY q.id
+1
source

Take a picture

 SELECT f.id, f.parent, f.faq, (SELECT COUNT(*) FROM faq_table f2 WHERE f2.id = f.parent) AS reply FROM faq f
0
source

Source: https://habr.com/ru/post/1340514/

More articles:


All Articles