How to write SELF JOIN on the same table in mysql

Question

I have a table named "comment", like bellow :

-----------------------------------

I want to all 'post' result with a extra column replies, where in replies column
count all reply where c_id = r_c_id, something like bellow:

you mean you have 2 tables with a column existind in both (like an ID" column) ? then just use `join` : http://dev.mysql.com/doc/refman/5.7/en/join.html — Seblor, Apr 09 '16 at 15:01
Posts need to be self-containing - include all necessary info directly in itself — juergen d, Apr 09 '16 at 15:01
Please - no pictures. Write the data in formatted code blocks. — Paul Spiegel, Apr 09 '16 at 15:08

Kamil Gosciminski · Answer 1 · 2016-04-09T15:15:56.183

1

This is a simple SELF JOIN to the same table with condition connecting c_id with rep_c_id:

SELECT
  t1.c_id, 
  t1.c_action,
  t1.rep_c_id,
  COUNT(t2.c_id) AS replies
FROM
  tablename t1
  LEFT JOIN tablename t2 ON t1.c_id = t2.rep_c_id
WHERE
  t1.c_action = 'post'
GROUP BY t1.c_id, t1.c_action, t1.rep_c_id

edited Apr 09 '16 at 15:15

answered Apr 09 '16 at 15:02

Kamil Gosciminski

16,547
8
49
72

It was my first version.I don't know what got me into changing this. I was somewhat afraid it wouldn't :o Thanks. – Kamil Gosciminski Apr 09 '16 at 15:15

How to write SELF JOIN on the same table in mysql

I have a table named "comment", like bellow :

1 Answers1