0

I am doing a full outer join in MySQL 5.7 using the answer from How to do a FULL OUTER JOIN in MySQL?.

However, I am not sure how to add aliases for sub-tables so that the query use the same table for the right join as it does for the left join (not even sure if MySQL will optimize this under the hood actually).

Code is below:

select *

from

(select cell as hdi_social_cell, count(*) as hdi_social_cell_count
from Detector_Social_Events where streamtype='social' and source = 'hdi'
group by cell having hdi_social_cell_count > 5) social_hdi

left join

(select cell as ml_social_cell, count(*)*0.1 as ml_social_cell_count
from Detector_Social_Events where streamtype='social' and source = 'ml'
group by cell having ml_social_cell_count > 1) social_ml

on social_hdi.cell = social_ml.cell

union select * from

(select cell as hdi_social_cell, count(*) as hdi_social_cell_count
from Detector_Social_Events where streamtype='social' and source = 'hdi'
group by cell having hdi_social_cell_count > 5)  social_hdi 

right join 

(select cell as ml_social_cell, count(*)*0.1 as ml_social_cell_count
from Detector_Social_Events where streamtype='social' and source = 'ml'
group by cell having ml_social_cell_count > 1)  social_ml

on social_hdi.cell = social_ml.cell;

The query as it is returns the error:

ERROR 1054 (42S22): Unknown column 'social_hdi.cell' in 'on clause'
asuprem
  • 554
  • 1
  • 5
  • 17

1 Answers1

2

Because you query select cell as hdi_social_cell

So you must to code

on social_hdi.hdi_social_cell = social_ml.hdi_social_cell
Ryan Nghiem
  • 2,417
  • 2
  • 18
  • 28