Mysql Query Question - combine two table results and sort alphabetically

[eluser]gscharlemann[/eluser]
I'm not quite sure how to create a query that will do the following:

I have two tables:
1) Contact Table with contact_id, first_name, last_name columns, etc.
2) Company Table with company_id, company_name columns, etc.

I want to display a list of both contacts and company names alphabetically by last_name and company name ascending.

So the result would look something like:

Alex Rodriguez
Dow Chemical Company
Jason Easton
Jack Johnson
My Company, Inc.
William Jones
etc.

I thought a join might be able to do it, but after some research, it doesn't look like it. Can someone point me in the right direction? Thanks

[eluser]WillKemp[/eluser]
You say you want to sort by people's last name, but your example sorts by first name, so i'll use that...

Try something like:

SELECT first_name AS ordered_name
, last_name
FROM Contact
UNION
SELECT company_name AS ordered_name
FROM Company
ORDER BY ordered_name

[eluser]gscharlemann[/eluser]
my bad, I guess Alex Rodriguez would be last... thanks. I'll give this a shot.

[eluser]gscharlemann[/eluser]
This worked, but it wanted me to provide a column for the company table to union with the first_name column. Is there a way to tell Mysql to insert a blank in the first_name column?

SELECT last_name AS ordered_name, first_name FROM contact WHERE user_id = 1 and deleted = 0 UNION
SELECT company_name AS ordered_name, -- NEED SECOND, BLANK COLUMN HERE -- FROM company WHERE user_id = 1 and deleted = 0 ORDER BY ordered_name, limit 0, 30

I guess I could do something like:
SELECT last_name AS ordered_name, first_name FROM contact WHERE user_id = 1 and deleted = 0 UNION
SELECT company_name AS ordered_name, company_name FROM company WHERE user_id = 1 and deleted = 0 ORDER BY ordered_name, limit 0, 30

and just ignore the second company_name. It feels cleaner to have blanks though.

Thanks for your help!

[eluser]WillKemp[/eluser]
Try:

SELECT concat( first_name , ' ' , last_name ) AS name
FROM Contact
UNION
SELECT company_name AS name
FROM Company
ORDER BY name

[eluser]gscharlemann[/eluser]
I realized I have another problem... I need to know if the result came from the contact table or the company table, because I handle the results a little differently depending on the type (contact or company). Currently, the results come back as follows:

Code:

ordered_name    | first_name
Bonds           | Bobby
Company 4       | Company 4
Henderson       | Bif
Ice             | Vanilla
Mayer           | John
My Comapny, Inc | My Comapny, Inc
Person          | NEw

Any ideas?

[eluser]gscharlemann[/eluser]
[quote author="WillKemp" date="1238895192"]Try:

SELECT concat( first_name , ' ' , last_name ) AS name
FROM Contact
UNION
SELECT company_name AS name
FROM Company
ORDER BY name[/quote]

Thanks Will.

[eluser]WillKemp[/eluser]
Or, of course, if you wanted to do it the other way...

SELECT first_name AS ordered_name
, last_name AS name2
FROM Contact
UNION
SELECT company_name AS ordered_name
, '' AS name2
FROM Company
ORDER BY ordered_name

[eluser]gscharlemann[/eluser]
I think adding blanks ('') in some columns will help determine the type as well. I'll play with that... good stuff!

[eluser]WillKemp[/eluser]
Of course, you could modify the last exmple to put " 'company' AS name2 " or something, instead of " '' AS name2 " - and then you can check for "company" in the results and you'll know it came from the company table. Just make sure no person's last name is "company"! ;-)