MySQL - Join & Count rows from another table

Question

I have 3 tables, like:
parent(id, name)
children(id, data, parent_id, timestamp)
table_votes(id, user_id, child_id)

I want to get all rows from children table that have a specific parent_id, showing also the count of occurences in table_votes for each one.

I try something like the following, but doesn't seem to work, I think I miss all rows from children that have no entry in table_votes

SELECT  
    `children`.`id`,  
    `children`.`data`,  
    `children`.`parent_id`,  
    `children`.`timestamp`,  
    COUNT(`v`.`children_id`)  
FROM `children` LEFT JOIN `table_votes` `v` ON `children`.`id` = `v`.`child_id`  
WHERE `children`.`parent_id` = 20 ORDER BY `timestamp` ASC

Any hints what am I doing wrong?

Thank you in advance

Uriil · Accepted Answer · 2014-05-19 10:59:05Z

5

There are few possible options, one of them:

SELECT * ,
  (SELECT count(*)
   FROM `table_votes`
   WHERE `children`.`id` = `table_votes`.`child_id`) AS `Count`
FROM `children`
WHERE `parent_id` = 20

You can use your query as well, but will have to add GROUP BY:

SELECT  
 `children`.`id`,  
 `children`.`data`,  
 `children`.`parent_id`,  
 `children`.`timestamp`,  
 COUNT(`v`.`children_id`)  
FROM `children` LEFT JOIN `table_votes` `v` ON `children`.`id` = `v`.`child_id`  
WHERE `children`.`parent_id` = 20 
GROUP BY `children`.`id`, `children`.`data`, `children`.`parent_id`, `children`.`timestamp`,
ORDER BY `timestamp` ASC

edited May 19, 2014 at 10:59

answered May 19, 2014 at 10:56

Uriil

12.8k11 gold badges54 silver badges73 bronze badges

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

Comments

georstef · Accepted Answer · 2014-05-19 11:01:36Z

2

1) solution with subselect:

SELECT  
    children.id,
    children.data,
    children.parent_id,  
    children.timestamp,  
    (select count(*) from table_votes children.id = table_votes.child_id) as cntVotes
FROM 
  children 
WHERE 
  children.parent_id = 20 
ORDER BY 
  children.timestamp ASC

2) solution with group by:

SELECT  
    children.id,
    children.data,
    children.parent_id,  
    children.timestamp,  
    count(table_votes.id) as cntVotes
FROM 
  children 
LEFT JOIN
  table_votes ON children.id = v.child_id
WHERE 
  children.parent_id = 20 
GROUP BY
    children.id,
    children.data,
    children.parent_id,  
    children.timestamp
ORDER BY 
  children.timestamp ASC

answered May 19, 2014 at 11:01

georstef

1,3883 gold badges12 silver badges18 bronze badges

Comments

Themba Mabaso · Accepted Answer · 2014-05-19 11:12:57Z

0

i am not sure if i am understanding your question correctly but to me it seems like you want to count the number of children in the tables_votes not in children. try the following:

select id, data, parent_id, timestamp, child_id
from table_votes join children on children_id.id = table_vote.child_id
where child_id = (select count(child_id) from table_vote)
and parent_id = '20'

answered May 19, 2014 at 11:12

Themba Mabaso

4114 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySQL - Join & Count rows from another table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related