MySQL Sorting

June 30, 2008 1:48 PM

Can I combine and sort MySQL search results?

I'm making an A-Z list of university departments. They're in a table with fields like this:

dept_name: Department of Engineering
alt_dept_name: Engineering Department

dept_name: Department of English
alt_dept_name: English Department

I can do a query that gets me every entry that has either dept_name or alt_dept_name that starts with a given letter. How can I combine the dept_names and alt_dept_names so they're sorted alphabetically together?

posted by kirkaracha to Computers & Internet (4 answers total)

union should do it
posted by zeoslap at 1:54 PM on June 30, 2008

(select dept_name as a from depts)
union
(select alt_dept a from depts)
order by a
posted by zeoslap at 1:58 PM on June 30, 2008

Personally, I'd use a numeric dept_ID field in the table that currently has your dept_name and alt_dept_name fields, and have a second table with dept_ID and dept_name fields in it. That way, not only would your departments be able to have an arbitrary number of names, but your queries would be more straightforward.

This won't be the last time you need to query as if dept_name and alt_dept_name were actually the same field. Best to make them so.
posted by flabdablet at 6:16 PM on June 30, 2008

What zeoslap said.
posted by orthogonality at 12:04 AM on July 1, 2008