Get the list of data from one table, and some count of records from another, is it possible to do in one query?

I have two tables - countries, tours.

countries has fields (id),(name),(order)

tours has fields (id),(id_country)...

I need to get the whole list of id and name from table countries ordered by their order, and the count of records in table tours, where tours.id_country = countries.id countries.

Ie, i开发者_如何学Go need to get such list

id   name      count_of_tours
1    France    15
2    England   22
.............................

Is it possible to do in one query?

Thanks much

SELECT C.id,
       C.name,
       COUNT(T.id) as count_of_tours
  FROM countries C
  LEFT JOIN tours T
         ON T.id_country = C.id
 GROUP BY C.id,
          C.name
 ORDER BY C.order

SELECT countries.id, countries.name, COUNT(id_country) AS Count
FROM countries
LEFT JOIN tours
on tours.id_country = countries.id
GROUP BY id_country
ORDER BY countries.order

This is real possible all you need to learn is the usage of joins. When you use join you can join the result from two tables and give the output as one.

SELECT id, name, COUNT(id_country) FROM countries LEFT JOIN tours on tours.id_country = countries.id order by countries.id;