Click here to Skip to main content
14,934,103 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Could anyone please show me an example of how to add a new counted column of each returned row of a referenced column from another table.

The below example is what I'm more or less looking for (doesn't work).
<pre lang="sql">SELECT
e.id,
e.video_id,
SELECT count(r.id) FROM reported as r where r.embed_id = e.id as countedItems
FROM videostreamer.embed e;




Result...
e.ID e.Video_ID countedItems
1.....20...........0
2.....22...........2
3.....23...........9
4.....25...........0
Posted
Updated 10-Mar-10 5:42am
v2

I eventually did it by adding a Function (if anyone would like to)...

<pre lang="sql">DELIMITER $$
DROP FUNCTION IF EXISTS `videostreamer`.`countReported` $$
CREATE FUNCTION `videostreamer`.`countReported` (aembed_ID int) RETURNS INTEGER
BEGIN

  DECLARE Temp INTEGER;
  SELECT count(id) INTO Temp FROM reported where embed_id = aembed_ID;
  RETURN IFNULL(Temp, 0);

END $$
DELIMITER ;





Then the sql was....

SELECT *, countReported(e.id) cReported  
FROM embed e 
where e.video_id = 368 and e.enabled = 1 and e.Parent_ID = 0 order by cReported;
   
SQL
SELECT    e.id, e.video_id, COUNT(r.embed_id) AS Count
FROM      reported AS r 
INNER JOIN
          [videostreamer.embed] AS e 
ON        r.embed_id = e.video_id
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900