Query Multiple Tables - Sort on Multiple Table Fields

Question

Hi all

OK, I know the data structure I am about to describe probably breaks all the rules of logic and normality, but this is a data retrieval operation only.

The situation is that I will have five queries, each possibly returning info from different tables.
Within each table there is an item number field. This corresponds to an item number ballooned on engineering drawing.
For example, the query on table 1 may return material details for items 1, 2 and 5, with the query on table 2 returning details for items 3 and 4.

I am guessing here, but do I need to query those five queries to be able to sort the results into item number order???
Also, at present the fields that contain those item numbers have labels specific to each table. So how do I query the five table queries to sort on the different item number fields and order by the result?

Hope this is all clear.

Thanks

Phil

Answer 1

Hi Phil. It probably won't make sense until you give it a try. If you don't alias the columns, the result of the UNION query will just use the field names of the first table you listed in the SQL statement. So, if you want the result to use a different column name, you can alias those fields. Here's an example:

SELECT Field1 AS F1, Field2 AS F2, Field3 AS F3 FROM Table1
UNION ALL
SELECT FieldA, FieldB, FieldC FROM Table2
UNION ALL
SELECT FirstName, MiddleName, LastName FROM Table3
ORDER BY F1, F2 F3

Hope that helps...

Answer 2

Hi Phil. Have you looked up UNION queries and "aliasing?"

Answer 3

Hi Phil. If you use a UNION query to combine all the data from multiple tables, you only need to alias the first table. All other tables will use the same column alias in the result.