How to join colums with different field ids?-CodePudding

Hi everyone. As you can see below I want to count the c.key_value with different c.config_field_id and relation_type_id. So I just want to join these two select statements. So as a result there should be shown 3 columns, they are:

| parent_id | count(c.key_value) | count(c.key_value(with another config_field_id, relation_type_id)) |

Please help me to solve this problem. Thanks in advance

select parent_id, count(c.key_value) from relation as r 
    left join config_value_number as c on r.child_id = c.key_value 
where c.config_field_id = 100 and relation_type_id = 150
group by parent_id

select parent_id, count(c.key_value) from relation as r 
       left join config_value_number as c on r.child_id = c.key_value 
where c.config_field_id = 101 and relation_type_id = 151 
group by parent_id

CodePudding user response：

You can simply use conditional aggregation for that:

select parent_id, 
count(case when c.config_field_id=100 then c.key_value end) as key_value_150, 
count(case when c.config_field_id=101 then c.key_value end) as key_value_151
from relation r 
       left join config_value_number c on r.child_id = c.key_value 
where (c.config_field_id =100 and relation_type_id =150) 
   or (c.config_field_id =101 and relation_type_id =151)
group by parent_id

CodePudding user response：

You would just need to join those two select statements instead of actual tables as subqueries.

SELECT parent_id, left_count, right_count
FROM (select parent_id, count(c.key_value) as left_count 
       from relation as r 
       left join config_value_number as c 
         on r.child_id = c.key_value 
       where c.config_field_id = 100 and relation_type_id = 150 group by parent_id) a
JOIN (select parent_id, count(c.key_value) as right_count 
       from relation as r 
       left join config_value_number as c 
         on r.child_id = c.key_value 
       where c.config_field_id = 101 and relation_type_id = 151 group by parent_id) b
ON a.parent_id = b.parent_id;