On Jun 21, 2007, at 8:08 PM, brian wrote:
Michael Glaesemann wrote:
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id | name
----+---------------------
8 | community
4 | dance
5 | film and television
9 | fine craft
7 | media arts
3 | music
6 | theatre
2 | visual arts
1 | writing
10 | other
(10 rows)
This relies on the fact that FALSE orders before TRUE. I don't
always remember which way, so I often have to rewrite it using <>
or = to get the behavior I want.
Of course! (slaps forehead)
I don't think you really need to use a function for this. I
believe you should be able to do this all in one SQL statement,
something like (if I've understood your query and intent correctly):
SELECT discipline.name, COUNT(showcase_id) AS total
FROM discipline
LEFT JOIN (
SELECT DISTINCT discipline_id, showcase.id as showcase_id
FROM showcase
JOIN showcase_item on (showcase.id = showcase_id)
WHERE accepted) AS accepted_showcases
ON (discipline.id = discipline_id)
GROUP BY discipline.name
ORDER BY discipline.name = 'other'
, discipline.name;
name | total
---------------------+-------
community | 0
dance | 0
film and television | 0
fine craft | 0
media arts | 0
music | 0
theatre | 0
visual arts | 1
writing | 2
other | 0
(10 rows)
That's bang on, Michael, thanks a bunch. I never remember to
explore joining on a select. I'm forever thinking in terms of
joining on a table. Things to study this evening.
One way to break yourself of that habit is to stop thinking in terms
of tables when you query and replace that thinking with relations.
Queries are made on relations and tables are only one kind of
relation. Then just remember that the results of select queries are
relations representing relationships between data in other relations
so they can themselves be used in select queries (as well as updates,
deletes and, as of 8.2, insert and copy statements).
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
