Re: Help with this query (some join stuff I think)

Pat Maddox wrote:
> 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.

You need an outer join and a subquery.

The following should give you the idea, but is untested:

SELECT
companies.id,
companies.name,
companies.nickname,
COUNT(v.company_id) AS num_videos
FROM companies
LEFT OUTER JOIN (SELECT * FROM videos WHERE status='complete') AS v
ON (companies.id=v.company_id)
GROUP BY
companies.id,
companies.name,
companies.nickname
ORDER BY num_videos DESC

Yours,
Laurenz Albe