Full outer join sample in MySQL - Replace a string in the dataset

Below is a sample query for doing a full outer join in MySQL:

The data joined from output_1 , output_2, output_3 and output_4 tables based on actor_link column.

We are also using the Replace() function of MySQL to replace a string in the resultant dataset.

Example Query:

select Replace(coalesce(out1.actor_link, out2.actor_link, out3.actor_link, out4.actor_link), 'http://www.twitter.com/',''), coalesce(cnt1,0)+ coalesce(cnt2,0) + coalesce(cnt3,0) + coalesce(cnt4, 0)

from

(select actor_link, count(gen_key) as cnt1 from output_1 group by actor_link) out1

left outer join

(select actor_link, count(gen_key) as cnt2 from output_2 group by actor_link) out2

on out1.actor_link = out2.actor_link

left outer join

(select actor_link , count(gen_key) as cnt3 from output_3 group by actor_link) out3

on out1.actor_link = out3.actor_link

left outer join

(select actor_link , count(gen_key) as cnt4 from output_4 group by actor_link) out4

on out1.actor_link = out4.actor_link

UNION

select Replace(coalesce(out1.actor_link, out2.actor_link, out3.actor_link, out4.actor_link), 'http://www.twitter.com/',''), coalesce(cnt1,0)+ coalesce(cnt2,0) + coalesce(cnt3,0) + coalesce(cnt4, 0)

from

(select actor_link, count(gen_key) as cnt1 from output_1 group by actor_link) out1

right outer join

(select actor_link, count(gen_key) as cnt2 from output_2 group by actor_link) out2

on out1.actor_link = out2.actor_link

right outer join

(select actor_link , count(gen_key) as cnt3 from output_3 group by actor_link) out3

on out1.actor_link = out3.actor_link

right outer join

(select actor_link , count(gen_key) as cnt4 from output_4 group by actor_link) out4

on out1.actor_link = out4.actor_link

where out1.actor_link <> 'actor__link'

;

Please let us know if you need any help in your Analytics exploits.