> From: Baron Schwartz > 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
Excellent - that does the trick! Thanks :) Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
