Hi Edward,
Edward Kay wrote:
Hi,
I have a query that returns data from a join of two tables, person and
company. The results look like:
FIRST_NAME | LAST_NAME | COMPANY_NAME
-------------------------------------
NULL | NULL | Toy Co
Mark | Smith | NULL
NULL | NULL | Big Corp
NULL | NULL | Acme Ltd
Lucy | Jones | NULL
I want to be able to order these results alphabetically by name
(first_name,last_name or company_name), regardless of whether they are a
person or company, to get:
FIRST_NAME | LAST_NAME | COMPANY_NAME
-------------------------------------
NULL | NULL | Acme Ltd
NULL | NULL | Big Corp
Lucy | Jones | NULL
Mark | Smith | NULL
NULL | NULL | Toy Co
If I ORDER BY first_name, last_name, company_name I get all the companies
followed by all the people (due to the NULLs in the first_name and last_name
fields).
Ideally I should be able to achieve this with ORDER BY
CONCAT(first_name,last_name,company_name) but of course this doesn't work
because CONCAT returns NULL if any of its arguments are NULL (which will
always be the case).
You could use COALESCE(). It returns the first non-null item in the list.
Baron
--
Baron Schwartz
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
