| From: | "Pat Maddox" <pergesu(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Help with this query (some join stuff I think) |
| Date: | 2007-08-16 19:59:05 |
| Message-ID: | 810a540e0708161259y48cd5756se0ce5ed1f6842c4e@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Page | 2007-08-16 20:18:36 | Re: Running a stored procedure via pgagent, need an examp le |
| Previous Message | Rainer Bauer | 2007-08-16 19:32:19 | Re: Yet Another COUNT(*)...WHERE...question |