From: | Carlos Ortíz <carlos(dot)ortiz(at)stps(dot)gob(dot)mx> |
---|---|
To: | "Pat Maddox" <pergesu(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Help with this query (some join stuff I think) |
Date: | 2007-08-17 15:50:19 |
Message-ID: | 17672AB7CE8E5649A043EF56118A63DC3D8671@CCEXGCLUS.stps.gob.mx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
?
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(at)postgresql(dot)org en nombre de Pat Maddox
Enviado el: Jue 16/08/2007 02:59 p.m.
Para: pgsql-general(at)postgresql(dot)org
Asunto: [GENERAL] 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2007-08-17 15:53:57 | Re: why it doesn't work? referential integrity |
Previous Message | Ted Byers | 2007-08-17 15:32:00 | Re: [RESEND] Transaction auto-abort causes grief with Spring Framework |