Help with this query (some join stuff I think)

Поиск
Список
Период
Сортировка
От Pat Maddox
Тема Help with this query (some join stuff I think)
Дата
Msg-id 810a540e0708161259y48cd5756se0ce5ed1f6842c4e@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
I've got a bunch of companies that are associated with several videos.
 The videos have different statuses.  I want to select all the
companies in the database, and order them by videos that have a
complete status.

Here's what I have so far

SELECT
  companies.id,
  companies.name,
  companies.nickname,
  COUNT(company_id) AS num_videos
FROM companies
LEFT JOIN videos ON companies.id=videos.company_id
GROUP BY
  companies.id,
  companies.name,
  companies.nickname
ORDER BY num_videos DESC
LIMIT 10

This orders the companies by number of videos...but it says nothing
about the video status.  If I include a
WHERE videos.status='complete'

then it filters out the companies that have no complete videos.  I
want to include those companies in the result set though, saying that
num_videos is 0.

Anyone know how to get that done?

Pat

В списке pgsql-general по дате отправления:

Предыдущее
От: Adam Endicott
Дата:
Сообщение: Re: Confusing performance of specific query
Следующее
От: Tyson Lloyd Thwaites
Дата:
Сообщение: Transaction auto-abort causes grief with Spring Framework