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

Question

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 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

Answer 1

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

Answer 2

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

Answer 3

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;