MySQL Join Help

[Baving]

Hi,

I have an SQL query:

select staff.*,managers.* from staff,managers where staff.user_id = '2' or managers.user_id = '2'

I am trying to select rows from the database where the userid in the staff table = 2 or the userid in the managers table = 2. There are rows in the staff table but not in the manager table. It is failing to return any rows.

Could anyone take a look, thanks!

[Lars_Berg]

select  'staff', <columns>  

   from staff 
 where user_id = 2
union all 
select 'manager', <columns>
   from managers 
 where user_id = 2

The number of columns in each select clause must be the same.

[Baving]

Thanks for that. However there are different fields in both tables and it errors:

 MySQL said: Documentation
#1271 - Illegal mix of collations for operation 'UNION' 

Is there any other way of doing this?

[Lars_Berg]

In that case you have to add an explicit collate clause to those columns that have different collations.