Perform full outer join in mysql

Question

I already know i can do outer join in MySQL using union. I also check this one. Full Outer Join in MySQL

But i want to do something like this and I don't know how can I achieve this using union.

I have db table user_count as follow.

+-------------+-----------+---------+-------+
| meb_id      | left_id   |right_id |active |    
+-------------+-----------+---------+-------+
| 1001        | (NULL)    | (NULL)  |  1    | 
| 1002        | 1001      | 0       |  0    | 
| 1003        | 0         | 1001    |  0    |
| 1004        | 1001      | 0       |  0    |
| 1004        | 1002      | 0       |  0    |
+-------------+-----------+---------+-------+

I have queries as follow.

    SELECT left_id, COUNT(left_id) AS left_count FROM `user_count` GROUP BY left_id 

    SELECT right_id, COUNT(right_id) AS right_count FROM `user_count` GROUP BY right_id;

SELECT left_id AS meb_id, COUNT(left_id) AS active_left_count FROM `user_count` WHERE active = 1 GROUP BY left_id;

SELECT right_id AS meb_id, COUNT(right_id) AS active_right_count FROM `user_count` WHERE active = 1 GROUP BY right_id;

I want to preform outer join or union so my result will be like this

+-------------+-----------+------------+------------+--------------+
| meb_id      |left_count |right_count |active_left |active_right  | 
+-------------+-----------+------------+------------+--------------+
| (NULL)      | 0         | 0          |  0         | 0            |
| 0           | 1         | 3          |  0         | 0            |
| 1001        | 2         | 1          |  0         | 0            | 
| 1002        | 1         | 0          |  0         | 0            |
| 1003        | 0         | 0          |  0         | 0            | 
+-------------+-----------+------------+------------+--------------+

How can i do this. Any help greatly appreciated.

Your result makes no sense for me. Why does 1001 have 2 right counts, not 1 ? What is with the 0 meb_id ? and null ? You need to give a formula, some logic, not letting us figure what you want — Cosmin
– Cosmin, Commented Sep 9, 2015 at 5:54
@Cosmin Sry actually its 1. By mistake I wrote 2. I am going to edit this. hope you now understand it. — Gaurang Ghinaiya
– Gaurang Ghinaiya, Commented Sep 9, 2015 at 6:03
@Cosmin Actually there is no formula. I only want count of left_id and right_id group by particular column. As you seen in result table I want count of all left and right id with and without active condition. — Gaurang Ghinaiya
– Gaurang Ghinaiya, Commented Sep 9, 2015 at 7:11

Praveen · Accepted Answer · 2015-09-09 09:01:30Z

1

Try this:

select 
    a.meb_id
    ,sum(case when a.meb_id = b.left_id then 1 else 0 end) left_count
    ,sum(case when a.meb_id = b.right_id then 1 else 0 end) right_count 
    ,sum(case when a.meb_id = b.left_id and active = 1 then 1 else 0 end) active_left  
    ,sum(case when a.meb_id = b.right_id and active = 1 then 1 else 0 end) active_right 
from (
    select meb_id from user_count union
    select left_id from user_count union
    select right_id from user_count
) a
cross join user_count b
group by a.meb_id
order by a.meb_id

Demo sqlfiddle

answered Sep 9, 2015 at 9:01

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Perform full outer join in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related