Simple MySQL join and sort -- but how?

webvandals

This is one of those stoopid little things that I just know is reeeally easy, but I just can't quite figure it out...

All I want to do is select 3 fields from 3 tables, and sort on 1 of those fields.

Simple example:
Table 'presidents' contains fields: 'name', 'age', 'gender'.

Table 'senators' contains the same 3 fields.

Table 'astronauts' also contains the same 3.

So how can I select those 3 fields from all 3 tables, sorted by age?

Thanks in advance!!

pete_bisby

SELECT 'President' AS Occupation, Name, Age, Gender FROM presidents
UNION SELECT 'Senator' AS Occupation, Name, Age, Gender FROM senators
UNION SELECT 'Astronaut' AS Occupation, Name, Age, Gender FROM astronauts;

With the use of UNION you have to make that you have the same number of fields reporting in each SELECT query, and that the fields are of the same type (e.g. you cannot have Age as the first field in the first query and Name as the first field in the second query).

webvandals

I'm a little confused. What does the "AS Occupation" part do? Also how can I get MySQL to sort by age? Can I just add "ORDER BY" to this query?

pete_bisby

The 'AS Occupation' basically renames the field, because it needs a description. If you didn't, then the name of the field would be 'President', simply because that is the content of the field. You can use the 'AS' option for any field within your SELECT statement.

And, yes, you can put the 'ORDER BY' at the end of the final SELECT query to sort by age.

Foo

Pete's system works by getting SQL combine all 3 tables into 1 big table, temporarily, for the purpose of this single query.

Just a quick note (in case you dont know this): The database structure you have created is not efficient. A better structure would be a single tablelike so:

Worker (
Job
Name
Age
Gender
);

I'm guessing that your tables are like this only for the purposes of an exercise. If so, disregard my comment. But if you're actually intending on implementing this system, the single table structure above is easier to work with.

webvandals

Thanks so much for your tips guys... I've been coding for 17 hours straight now, so I'll put your advice into action tomorrow and let you know how it goes... G'night...

webvandals

With your help I was able to get my query working perfectly! Thank you sooo much!!!

In case it helps anyone else, here's the query that solved all my problems (note: the use of UNION requires MySQL 4.0 and up):

SELECT 'pay' AS type, account_id, date, amount, comments
  FROM payments
  WHERE account_id='$acc'
UNION ALL
SELECT 'inv' AS type, account_id, date, amount, comments
  FROM invoices
  WHERE account_id='$acc'
UNION ALL
SELECT 'not' AS type, account_id, date, NULL AS amount, comments
  FROM notes
  WHERE account_id='$acc'
ORDER BY date DESC";