两个子查询添加到添加复杂的mySQL查询返回没有记录 [英] Two subqueries added to addition complex mySQL query returning no records

问题描述

我有这个代码，单独他们都工作，所以我认为它必须是不正确的语法：

  SELECT i。*，o.organ_name，o.organ_logo，vtable。*，cc.ccount 
 FROM heroku_056eb661631f253.op_ideas i 
 JOIN 
  

上面从op_ideas表中收集所有记录。

  .idea_Id，
 COUNT（v.agree = 1或null）as agree，
 COUNT（v.disagree = 1或null）as disagree，
 COUNT（v.obstain = 1 or null）as agree， ）as abstain 
 FROM op_idea_vote v 
 GROUP BY v.idea_id 
）AS vtable ON vtable.idea_id = i.idea_id 
  

然后，上面搜索另一个表，并计算每个记录的投票数，并将其添加到该行。

  JOIN 
（SELECT ccc.idea_id AS cid，COUNT（ccc.idea_id = 1或null）AS ccount 
 FROM op_comments ccc 
 GROUP BY idea_id 
 ）AS cc ON cid = i.idea_id 
  

上述计算有多少注释附加到idea_id

  LEFT JOIN op_organs o ON i.post_type = o.organs_id 
  

上述将另一个表连接到现有行，可能为空或不为空

  WHERE idea_geo ='国际'; 
  

国际以上被替换为可以等于本地，地区，国家或国际的变量。 p>

发出：查询触发但回到空，但如果单独放置，它们会起作用。

这是完整的代码，以帮助阅读：

 这里是另一个问题，我认为我把代码放在错误的地方。想要添加另一个子SELECT来计算每个想法有多少注释：
 
 SELECT i。*，o.organ_name，o.organ_logo，vtable。*，cc.ccount 
 FROM heroku_056eb661631f253。 op_ideas i 
 JOIN 
（SELECT v.idea_Id，
 COUNT（v.agree = 1或null）as agree，
 COUNT（v.disagree = 1 or null）as disagree ，
 COUNT（v.obstain = 1或null）as abstain 
 FROM op_idea_vote v 
 GROUP BY v.idea_id 
）AS vtable ON vtable.idea_id = i.idea_id 
 JOIN 
（SELECT ccc.idea_id AS cid，COUNT（ccc.idea_id = 1或null）AS ccount 
 FROM op_comments ccc 
 GROUP BY idea_id 
）AS cc ON cid = i.idea_id 
 LEFT JOIN op_organs o ON i.post_type = o.organs_id 
 WHERE idea_geo ='International';提前感谢。
 
 
 
  
   >编辑新解决方案 
 
 
 
由于WayneC和Conrad，我们有一个全面的查询。
 
 
 
这是代码：
  SELECT i。*，o.organ_name，o.organ_logo，vtable。* 
 FROM heroku_056eb661631f253.op_ideas i 
 LEFT JOIN 
（SELECT v.idea_Id，cc。*，
 COUNT（v.agree = 1或null）as agree，
 COUNT（v.disagree = 1或null）不同意，
 COUNT（v.obstain = 1或null）as abstain 
 FROM op_idea_vote v 
 LEFT JOIN 
（SELECT idea_id AS id，COUNT ）AS ccount 
 FROM op_comments cco 
 GROUP BY cco.idea_id 
）AS cc ON cc.id = v.idea_id 
 GROUP BY v.idea_id 
）AS vtable ON vtable.idea_id = i.idea_id 
 LEFT JOIN op_organs o ON i.post_type = o.organs_id 
 WHERE idea_geo ='International'; 
  
 
 
解决方案
 
 
 
  SELECT i。*，o.organ_name，o.organ_logo，vtable。* 
 FROM heroku_056eb661631f253.op_ideas i 
 LEFT JOIN 
（SELECT v.idea_Id，cc。*，
 COUNT（v.agree = 1或null）as agree，
 COUNT（v.disagree = 1 or null）as disagree，
 COUNT（v.obstain = 1或null）as abstain 
 FROM op_idea_vote v 
 LEFT JOIN 
（SELECT idea_id AS id，COUNT（*）AS ccount 
 FROM op_comments cco 
 GROUP BY cco.idea_id 
）AS cc ON cc.id = v.idea_id 
 GROUP BY v.idea_id 
）AS vtable ON vtable.idea_id = i.idea_id 
 LEFT JOIN op_organs o ON i.post_type = o.organs_id 
 WHERE idea_geo ='International'; 
  
 
I have this code, and individually they all work, so I think it must be the syntax which is incorrectly placed:
SELECT i.*, o.organ_name, o.organ_logo, vtable.*, cc.ccount
FROM heroku_056eb661631f253.op_ideas i
JOIN
The above collects all records from op_ideas table.
(SELECT v.idea_Id,
    COUNT(v.agree = 1 or null) as agree,
    COUNT(v.disagree = 1 or null) as disagree,
    COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v
GROUP BY v.idea_id
) AS vtable ON vtable.idea_id = i.idea_id
The above then searches another table and counts the votes for each record and adds it to the row.
JOIN 
(SELECT ccc.idea_id AS cid, COUNT(ccc.idea_id = 1 or null) AS ccount 
FROM op_comments ccc
GROUP BY idea_id
) AS cc ON cid = i.idea_id
The above counts how many comments are attached to the idea_id and adds it to the main row.
LEFT JOIN op_organs o ON i.post_type = o.organs_id
The above joins another table to the existing row, which may or may not be blank
WHERE idea_geo = 'International';
International above is replaced with a variable which could equal: Local, Regional, National or International.

Issue: The query fires but comes back empty, but they work if put individually.  Can someone please point me in the right direction.

Here is the full code to help read it:
Here is another issue, I think I have placed the code in the wrong place.  Wanting to add another sub SELECT to count how many comments are per idea:

SELECT i.*, o.organ_name, o.organ_logo, vtable.*, cc.ccount
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT v.idea_Id,
    COUNT(v.agree = 1 or null) as agree,
    COUNT(v.disagree = 1 or null) as disagree,
    COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v
GROUP BY v.idea_id
) AS vtable ON vtable.idea_id = i.idea_id
JOIN 
(SELECT ccc.idea_id AS cid, COUNT(ccc.idea_id = 1 or null) AS ccount 
FROM op_comments ccc
GROUP BY idea_id
) AS cc ON cid = i.idea_id
LEFT JOIN op_organs o ON i.post_type = o.organs_id
WHERE idea_geo = 'International';
Thanks in advance.

EDIT NEW SOLUTION

Thanks to WayneC and Conrad we have a fully working query.

Here is the code:
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
LEFT JOIN
(SELECT v.idea_Id, cc.*,
    COUNT(v.agree = 1 or null) as agree,
    COUNT(v.disagree = 1 or null) as disagree,
    COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v 
LEFT JOIN 
    (SELECT idea_id AS id,COUNT(*) AS ccount 
    FROM op_comments cco
    GROUP BY cco.idea_id
    ) AS cc ON cc.id = v.idea_id
GROUP BY v.idea_id
) AS vtable ON vtable.idea_id = i.idea_id
LEFT JOIN op_organs o ON i.post_type = o.organs_id
WHERE idea_geo = 'International';

解决方案
And the answer is as follows:
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
LEFT JOIN
  (SELECT v.idea_Id, cc.*,
    COUNT(v.agree = 1 or null) as agree,
    COUNT(v.disagree = 1 or null) as disagree,
    COUNT(v.obstain = 1 or null) as abstain
    FROM op_idea_vote v 
LEFT JOIN 
  (SELECT idea_id AS id,COUNT(*) AS ccount 
    FROM op_comments cco
    GROUP BY cco.idea_id
    ) AS cc ON cc.id = v.idea_id
  GROUP BY v.idea_id
  ) AS vtable ON vtable.idea_id = i.idea_id
  LEFT JOIN op_organs o ON i.post_type = o.organs_id
WHERE idea_geo = 'International';


                        
这篇关于两个子查询添加到添加复杂的mySQL查询返回没有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！