Order by cbk.DateInitiated,invoices. I have tried adding and Order clause at the bottom i.e. Each SELECT statement within the MySQL UNION ALL operator must have the same number of fields in the result sets with similar data types. When you do a select on a join on the tables you will get duplicate column names. Aliasing can also be used when youre using computed values, such as Column1 + Column2 AS Column3. In this case, your example means youre returning a column that always contains NULL, and its alias/column name is aColumn.

It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements. Seems you have a column called CatID in both cats and bills tables. Aliasing can be used in a number of ways, not just to shorten a long column name.

(`invoices`.`CustSuppCode` = _latin1'FRE001') The MySQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. `invoices`.`PaymentAmount` AS `PaymentAmount` `invoices`.`CustSuppCode` AS `CustSuppCode`, `invoices`.`DateInitiated` AS `DateInitiated`, (`cbk`.`CustSuppCode` = _latin1'FRE001') union Also, if a column to be sorted is aliased, the refer to the alias, not the column name. `cbk`.`DateInitiated` AS `DateInitiated`, Irrespective of which table the row comes from: The following query works but the problem is that it shows all the receipts first and then all the invoices thereafter, what i need is for it to return the data in date order

I am trying to pull invoice amounts from one table and union these vs receipts from another table.

Mysql union with alias column names