OUTER JOIN equivalent in MySQL -

OUTER JOIN equivalent in MySQL -

i have 2 tables in mysql database representation of clustering of people. tables c_orig , c_now map person_id cluster number, this:

person_id cluster 0 0 1 0 2 1 ... ...

i want know how many people changed cluster (where came or went to) when comparing c_now c_orig. can select influx , outflux next 2 queries.

set @cluster = 0; select c_orig.cluster, count(c_now.person_id) influx c_now left bring together c_orig on c_orig.person_id = c_now.person_id c_now.cluster = @cluster , (c_orig.cluster != @cluster or c_orig.cluster null) grouping c_orig.cluster; select c_now.cluster, count(c_now.person_id) outflux c_now left bring together c_orig on c_orig.person_id = c_now.person_id c_orig.cluster = @cluster , c_now.cluster != @cluster grouping c_now.cluster;

results of these queries:

cluster influx null 39 1 8 2 5 3 2 4 16 6 9 7 2 8 1 cluster outflux 1 9 2 7 3 46 4 5 6 13 7 2 8 1 9 5

now want combine these queries, , have influx/outflux of 0 when cluster not nowadays in query. however, cannot outer join them, , 2 left joins different order seems cumbersome. guess overlooking easy way accomplish that. can help me out? desired output:

cluster influx outflux null 39 0 1 8 9 2 5 7 3 2 46 4 16 5 6 9 13 7 2 2 8 1 1 9 0 5

you can using left join, need total list of clusters. given queries, can as:

(select cluster c_now union select cluster c_orig ) c

you generate query as:

select c.cluster, coalesce(q1.influx, 0) influx, coalesce(q2.outflux, 0) outflux (select cluster c_now union select cluster c_orig ) c left bring together (query1) q1 left bring together on c.cluster = q1.cluster (query2) on c.cluster = q2.cluster;

i leaving out details of queries can see overall construction of query. substitute queries query1 , query2.

mysql outer-join