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

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

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