CODEWITHSUNDEEP
mysqljoinmergeunion
user1195745
user1195745

Reputation: 65

mysql union to not working

Hi im trying to merge two tables.

I have ids that exists only in the left table and i have ids that only exists in the right table. I want to get get one table with all ids. I tried:

select * from temp_hours as a
    left outer join temp_orders as o on a.proj_id = o.proj_id
union
select * from temp_hours as a
    right outer join temp_orders as o on a.proj_id = o.proj_id

But i get the error:

Can't reopen table: 'a'

any ideas how i can do this? Server version: 5.0.51a-24+lenny4 (Debian)

Upvotes: 3

Views: 6978

Answers (3)

manurajhada
manurajhada

Reputation: 5380

Add the Parenthesis as suggested or change the Abbreviation...

select * from temp_hours as a
    left outer join temp_orders as ao on a.proj_id = ao.proj_id
union
select * from temp_hours as b
    right outer join temp_orders as bo on b.proj_id = bo.proj_id

Upvotes: 3

bitoshi.n
bitoshi.n

Reputation: 2318

You need parenthesis for every select

(select * from temp_hours as a
    left outer join temp_orders as o on a.proj_id = o.proj_id)
union
(select * from temp_hours as a
    right outer join temp_orders as o on a.proj_id = o.proj_id)

But, error that you get seems temporary table limitation.

Upvotes: -1

Sirko
Sirko

Reputation: 74046

Just add some parenthesis around your select queries:

(select * from temp_hours as a
    left outer join temp_orders as o on a.proj_id = o.proj_id)
union
(select * from temp_hours as a
    right outer join temp_orders as o on a.proj_id = o.proj_id)

See, e.g., the examples in the docu.

Upvotes: 3

Related Questions