Hi!
I have two tables, one containing information about people, and one containing information about companies. A very simplified version of my tables would look like this;
people;
id int auto_increment,
company_id int,
company_name varchar(128)
company;
id int auto_increment,
company_name varchar(128)
Now, the people table contains information about a person, and a link to the company *if any*. The company_name field may be filled in if company_id is 0. Now the problem; I need to sort this by company name. (I.e. "select * from people order by company_name", but sort on company_name from 'company' if any).
I know I could code this in PHP instead of sorting it in the select, but that would be pretty inefficient with 50k records
I suppose this could be solved with a view in i.e. postgresql, but I only have mysql available. I suspect that I could use CASE WHEN. Can anyone give me some pointers?
I have two tables, one containing information about people, and one containing information about companies. A very simplified version of my tables would look like this;
people;
id int auto_increment,
company_id int,
company_name varchar(128)
company;
id int auto_increment,
company_name varchar(128)
Now, the people table contains information about a person, and a link to the company *if any*. The company_name field may be filled in if company_id is 0. Now the problem; I need to sort this by company name. (I.e. "select * from people order by company_name", but sort on company_name from 'company' if any).
I know I could code this in PHP instead of sorting it in the select, but that would be pretty inefficient with 50k records
I suppose this could be solved with a view in i.e. postgresql, but I only have mysql available. I suspect that I could use CASE WHEN. Can anyone give me some pointers?