ORDER BY in UNION query

I seen topics explaining this but in my case it does not work.

I have query

( SELECT * FROM my_table
left join table2 on table2.id = my_table.id
left join table3 on pension.age = my_table.age

  WHERE table3.id IS NULL )

UNION

( SELECT * FROM my_table
left join table2 on table2.id = my_table.id
left join table3 on pension.age = my_table.age
  WHERE my_table.id FROM 75 to 245 )

ORDER BY my_table.id ASC, table2.wage DESC, table3.compensation DESC

this does not work saying user_table. or table2. or table3. not found

when i remove it its saying

ORDER BY id ASC, wage DESC, compensation DESC

this somewhat works but not desi开发者_Python百科red result. please assist

Is there part of the code missing? I see no reference to table_fired. Also, aren't those curly braces used as part of an outer join in a larger query? That's why I think there's a larger part of the query missing, which might be relevant.

SELECT * FROM my_table
    left join table2 on table2.id = my_table.id
    left join table3 on pension.age = my_table.age
WHERE my_table.id IS NULL OR my_table.id FROM 75 to 245
ORDER BY my_table.id ASC, table2.wage DESC, table3.compensation DESC

I replaced your "table_fired" with "my_table" and combined the two subselects into one.

The union operation requires that each of your two queries have exactly the same number of columns in their result set. In mysql, UNION will always use the column names from the frist query - so if the second query uses different column names, they will be mapped by order onto the columns that were defined by the first query.

Your ORDER BY will be applied after the UNION has been run, and so it can only refer to columns that are in the result set of the UNION. These columns are not qualified by table identifiers from the constituent queries (that's why removing the table identifiers from your ORDER BY clause gets rid of the explicit errors).

Beyond that, the problem is likely that your component queries produce multiple columns that have the same name, and are distinguishable only by their table identifiers (for example my_table.id and table2.id). When you use ORDER BY id ASC ..., which of those "id" fields will be used?

Solve this problem by replacing the * with an explicit list of the relevant columns for each of the two component queries. Ensure that each column you select is given a unique name. For Example:

SELECT 
  my_table.id as my_table_id, 
  table2.id as table2_id, 
  table2.compensation as compensation,
  table3.wage as wage
...

Your union will then pick up distinctly named columns, and your order by clause would need to refer to those instead of the table-qualified columns in the original queries:

ORDER BY my_table_id ASC, wage DESC, compensation DESC