MySQL – Join & Count rows from another table

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 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

Method 3

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'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0