Ordering one table query by another table.

[craftymind]

I have one table 'country' in mysql and another table 'employee'.
What I want to do is display all the employees by alphabetical ordering of countries but don't know how to do it.
So my table country is

[code=php]

id | cname

1 | America
2 | India
3 | England
[/code]
And my employee table is

[code=php]

id | countryid | ename

1 | 3 | Ashby
2 | 3 | Neal
3 | 2 | Manoj
4 | 1 | Richards
5 | 3 | Dalton
6 | 1 | Crandel
[/code]

Is there a single SELECT statement that would allow me to output this? Or would I have Select from country order by cname and then for each country row select from employees?

America
- Crandel
- Richards
England
- Ashby
- Dalton
- Neal
India
- Manoj

[Sxooter]

The easiest way is to join the two tables and order by the result set by country:

select * from employee join country on (employee.countryid =country.id) order by country.name;

then, when printing them out, to get the cool country1:
username1
username2
country2:
username3

layout, you can use a while statement:

$country_test = result($res,0,'country');
$country = "bogus country name";
for ($i=0;$i<$count_of_recs;$i++){
  if ($country_test!=$country) {
    print "<div>".$countr.":<BR>";
    $country_test = $country;
  }
  $country = result($res,$i,'country');
  print result($res,$i,'personname')."<BR>";
}

something like that.

[tsinka]

Hi,

you might want to change the sql query to

select * from employee join country on (employee.countryid =country.id) order by country.name,employee.ename;

that would order by countries and for each country by employee if there is more than one employee in one country.

Thomas

[craftymind]

I've finally been able to try out this code, thanks for the help!