Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Help with this query (some join stuff I think)
?
Try some thing like ths:
SELECT
  companies.id,
  companies.name,
  companies.nickname,
  (Select count(*) from videos where companies.id=videos.company_id and videos.status= 'complete')  num_videos
FROM companies
ORDER BY num_videos DESC

Hope this help
Carlos E. Ortiz


De: pgsql-general-owner@xxxxxxxxxxxxxx en nombre de Pat Maddox
Enviado el: Jue 16/08/2007 02:59 p.m.
Para: pgsql-general@xxxxxxxxxxxxxx
Asunto: 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux