MySql query on 3 tables not returning all rows

Question

I have 3 tables: questions, options, comments_to_options(opt_comments). I want to write a query that returns in each row the following values, concatenated:

A question, all options to it, all comments to each option.

My query is:

select 
concat('{', '"qid":"', q.q_id, '", "qt":"', q.q_title,
'", "op":[', group_concat('{"oi":"', o.op_id, '", "ot":"', o.opt_value, '", ', oc_list, '}' 
order by o.opt_upvotes desc), ']}') 
as r 
from questions q, options o,
(select o.op_id as ocid, concat('"oc":[', group_concat('{"oci":"', oc.opt_com_id, '", "occ":"', oc.opt_com_value, '"}' 
order by oc.opt_com_added_at), ']') 
as oc_list 
from options o, opt_comments oc 
where oc.opt_com_to=o.op_id 
group by o.op_id)
as r2
where o.op_id=r2.ocid 
and q.q_id=o.option_to 
group by q.q_id
order by q.q_added_at desc
limit 3;

But the above query gives only those options that have at least one comment to them. How should I modify?

Answer 1

You are using the old JOIN syntax with comma-separated lists of tables and subqueries. That syntax is correct, but generates INNER JOIN operations. Such joins suppress rows that don't match the join criterion.

You need to adopt the LEFT JOIN syntax. Without refactoring your entire query, I will say that you should change

 FROM a, 
      (select something from z) AS b
WHERE a.value=b.value 

to

 FROM a 
 LEFT JOIN (select something from z) AS b ON a.value=b.value

Also, beware, you may encounter the character-string length limit in GROUP_CONCAT(). Read this:

MySQL and GROUP_CONCAT() maximum length

Answer 2

Use "left join". Example:

create table opt (oid int,name varchar(100));
insert into opt values (1,'opt1');
insert into opt values (2,'opt2');
insert into opt values (3,'opt3');

create table optcom (oid int,com varchar(100));
insert into optcom values (1,'opt1_1');
insert into optcom values (1,'opt1_2');
insert into optcom values (3,'opt3_1');

When using "simple join":

 select opt.*,optcom.* from opt join optcom on opt.oid=optcom.oid;

+------+------+------+--------+ | oid | name | oid | com | +------+------+------+--------+ | 1 | opt1 | 1 | opt1_1 | | 1 | opt1 | 1 | opt1_2 | | 3 | opt3 | 3 | opt3_1 | +------+------+------+--------+ When "left join":

  select opt.*,optcom.* from opt left join optcom on opt.oid=optcom.oid;

+------+------+------+--------+ | oid | name | oid | com | +------+------+------+--------+ | 1 | opt1 | 1 | opt1_1 | | 1 | opt1 | 1 | opt1_2 | | 2 | opt2 | NULL | NULL | | 3 | opt3 | 3 | opt3_1 | +------+------+------+--------+

Answer 3

To follow up on the above responses, the SQL amended to use outer joins:-

SELECT CONCAT('{', '"qid":"', q.q_id, '", "qt":"', q.q_title,'", "op":[', GROUP_CONCAT('{"oi":"', o.op_id, '", "ot":"', o.opt_value, '", ', oc_list, '}' ORDER BY o.opt_upvotes DESC), ']}') AS r 
FROM options o
LEFT OUTER JOIN questions q
ON q.q_id = o.option_to 
LEFT OUTER JOIN 
(
    SELECT o.op_id AS ocid, 
            CONCAT('"oc":[', GROUP_CONCAT('{"oci":"', oc.opt_com_id, '", "occ":"', oc.opt_com_value, '"}' ORDER BY oc.opt_com_added_at), ']') AS oc_list 
    FROM options o
    INNER JOIN opt_comments oc 
    ON oc.opt_com_to=o.op_id 
    GROUP BY o.op_id
) r2
ON o.op_id = r2.ocid 
GROUP BY q.q_id
ORDER BY q.q_added_at DESC
LIMIT 3;

Looking at this I am unsure about the join to the sub query. This appears to be bringing back an encoded string, but beyond the actual join nothing from this sub query is actually used.

As such I am unsure if that sub query is just being used to narrow down the rows returned (in which case joining against it using an INNER JOIN would be appropriate - and you may as well not bring back the encoded string), or if you have posted a cut down version of the query that you have been trying to debug.