mysql - SQL union select multiple colomns -

mysql - SQL union select multiple colomns -

i trying select both score , user_id 2 identical tables (execpt table names)

select user_id, score score ( select tes.score test_score tes inner bring together user u on u.id = tes.user_id u.organization_id = 7 union select tas.score task_score tas inner bring together user u on u.id = tas.user_id u.organization_id = 7 ) subquery order score desc limit 10

however when run next error:

error code: 1054. unknown column 'user_id' in 'field list'

the table this:

table: test_score columns: id int(11) pk ai user_id int(11) module_id int(11) score double number_correct int(11) correct_total int(11) timestamp datetime phase_id int(11) medal_id int(11) team_id int(11) status_id int(11)

your inner query doesn't homecoming columns called user_id, it's not possible outer query access it.

try instead:

select user_id, score score ( select tes.user_id, tes.score test_score tes inner bring together user u on u.id = tes.user_id u.organization_id = 7 union select tas.user_id, tas.score task_score tas inner bring together user u on u.id = tas.user_id u.organization_id = 7 ) subquery order score desc limit 10

mysql sql