Re: [SQL] cross tables, SELECT expressions, and GROUP BY problem
On Sun, 2006-04-02 at 16:43 +1000, Ross Johnson wrote:
> Hi,
>
> I'm relatively stretched when it comes to SQL but I'm getting there. I
> have a question though:
>
> Thanks to various web sites I've succeeded in creating simple cross
> tables in PostgreSQL. I'm now trying more complicated examples
> (migrating queries from MS Access to PostgreSQL). I'm stuck on getting
> grouping to work where the selection element isn't a real table field,
> where it is generated by a CASE statement. Here's a full actual SELECT
> statement, that works but isn't quite what I need, followed by the first
> few rows of output:
>
> SELECT DISTINCT
> CASE
> WHEN lower(c."Order") = 'coleoptera' THEN 5
> WHEN lower(c."Order") = 'trichoptera' THEN 8
> WHEN lower(c."Order") = 'ephemeroptera' THEN 6
> WHEN lower(c."Order") = 'plecoptera' THEN 7
> WHEN lower(c."Class") = 'oligochaeta' THEN 1
> WHEN lower(c."Family") LIKE 'chiron%' THEN 2
> WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> LIKE 'chiron%' THEN 3
> ELSE 4
> END AS "Ranking",
> CASE
> WHEN lower(c."Order") = 'coleoptera' THEN 'Coleoptera'
> WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'
> WHEN lower(c."Order") = 'ephemeroptera' THEN 'Ephemeroptera'
> WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'
> WHEN lower(c."Class") = 'oligochaeta' THEN 'Oligochaeta'
> WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'
> WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> LIKE 'chiron%' THEN 'Diptera (Other)'
> ELSE 'Other'
> END AS "Taxa",
> SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
> "011",
> SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
> "012",
> SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
> "013",
> SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
> "014"
> FROM "tblBugIDSheetInfo" b
> INNER JOIN "tblBugCount" c USING ("BugSheetID")
> GROUP BY c."Order", c."Class", c."Family"
> ORDER BY "Ranking"
>
> Generates the following output:
>
> Ranking Taxa011 012 013 014
> ---
> 1 "Oligochaeta" 487 171117591078
> 1 "Oligochaeta" 7
> 1 "Oligochaeta"
> 2 "Chironomidae" 1385233515001513
> 2 "Chironomidae"
> 3 "Diptera (Other)" 5
> 3 "Diptera (Other)" 1 1 3
> 3 "Diptera (Other)" 199 19 40 37
> 3 "Diptera (Other)"
> ...
I should add that I've just tried the following query, which is just a
rearrangement of the above query using a sub-SELECT, and the result is
the same as above apart from a slightly different ordering of the rows.
That is, GROUP BY "Ranking" still doesn't appear to do anything. I was
under the impression that a sub-SELECT creates a temporary, or at least
a pseudo-temporary table (a "virtual" table as it's called in the
documentation), so I would have expected this query to work even if the
above one doesn't.
SELECT DISTINCT
"Ranking", "Taxa",
SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
"011",
SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
"012",
SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
"013",
SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
"014"
FROM "tblBugIDSheetInfo" b
INNER JOIN (
SELECT "BugSheetID",
CASE
WHEN lower("Order") = 'coleoptera' THEN 5
WHEN lower("Order") = 'trichoptera' THEN 8
WHEN lower("Order") = 'ephemeroptera' THEN 6
WHEN lower("Order") = 'plecoptera' THEN 7
WHEN lower("Class") = 'oligochaeta' THEN 1
WHEN lower("Family") LIKE 'chiron%' THEN 2
WHEN lower("Order") = 'diptera' AND lower("Family") NOT
LIKE 'chiron%' THEN 3
ELSE 4
END AS "Ranking",
CASE
WHEN lower("Order") = 'coleoptera' THEN 'Coleoptera'
WHEN lower("Order") = 'trichoptera' THEN 'Trichoptera'
WHEN lower("Order") = 'ephemeroptera' THEN
'Ephemeroptera'
WHEN lower("Order") = 'plecoptera' THEN 'Plecoptera'
WHEN lower("Class") = 'oligochaeta' THEN 'Oligochaeta'
Re: [SQL] cross tables, SELECT expressions, and GROUP BY problem
Following up my own question again, I've realised my error and solved my
problem - in the interests of completing this thread, the working query
(two versions) can be found below...
On Sun, 2006-04-02 at 18:00 +1000, Ross Johnson wrote:
> On Sun, 2006-04-02 at 16:43 +1000, Ross Johnson wrote:
> > Hi,
> >
> > I'm relatively stretched when it comes to SQL but I'm getting there. I
> > have a question though:
> >
> > Thanks to various web sites I've succeeded in creating simple cross
> > tables in PostgreSQL. I'm now trying more complicated examples
> > (migrating queries from MS Access to PostgreSQL). I'm stuck on getting
> > grouping to work where the selection element isn't a real table field,
> > where it is generated by a CASE statement. Here's a full actual SELECT
> > statement, that works but isn't quite what I need, followed by the first
> > few rows of output:
> >
> > SELECT DISTINCT
> > CASE
> > WHEN lower(c."Order") = 'coleoptera' THEN 5
> > WHEN lower(c."Order") = 'trichoptera' THEN 8
> > WHEN lower(c."Order") = 'ephemeroptera' THEN 6
> > WHEN lower(c."Order") = 'plecoptera' THEN 7
> > WHEN lower(c."Class") = 'oligochaeta' THEN 1
> > WHEN lower(c."Family") LIKE 'chiron%' THEN 2
> > WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> > LIKE 'chiron%' THEN 3
> > ELSE 4
> > END AS "Ranking",
> > CASE
> > WHEN lower(c."Order") = 'coleoptera' THEN 'Coleoptera'
> > WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'
> > WHEN lower(c."Order") = 'ephemeroptera' THEN 'Ephemeroptera'
> > WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'
> > WHEN lower(c."Class") = 'oligochaeta' THEN 'Oligochaeta'
> > WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'
> > WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> > LIKE 'chiron%' THEN 'Diptera (Other)'
> > ELSE 'Other'
> > END AS "Taxa",
> > SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
> > "011",
> > SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
> > "012",
> > SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
> > "013",
> > SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
> > "014"
> > FROM "tblBugIDSheetInfo" b
> > INNER JOIN "tblBugCount" c USING ("BugSheetID")
> > GROUP BY c."Order", c."Class", c."Family"
> > ORDER BY "Ranking"
> >
> > Generates the following output:
> >
> > Ranking Taxa011 012 013 014
> > ---
> > 1 "Oligochaeta" 487 171117591078
> > 1 "Oligochaeta" 7
> > 1 "Oligochaeta"
> > 2 "Chironomidae" 1385233515001513
> > 2 "Chironomidae"
> > 3 "Diptera (Other)" 5
> > 3 "Diptera (Other)" 1 1 3
> > 3 "Diptera (Other)" 199 19 40 37
> > 3 "Diptera (Other)"
> > ...
>
> I should add that I've just tried the following query, which is just a
> rearrangement of the above query using a sub-SELECT, and the result is
> the same as above apart from a slightly different ordering of the rows.
> That is, GROUP BY "Ranking" still doesn't appear to do anything. I was
> under the impression that a sub-SELECT creates a temporary, or at least
> a pseudo-temporary table (a "virtual" table as it's called in the
> documentation), so I would have expected this query to work even if the
> above one doesn't.
>
>
> SELECT DISTINCT
> "Ranking", "Taxa",
> SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
> "011",
> SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
> "012",
> SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
> "013",
> SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
> "014"
> FROM "tblBugIDSheetInfo" b
> INNER JOIN (
> SELECT "BugSheetID",
> CASE
> WHEN lower("Order") = 'coleoptera' THEN 5
> WHEN lower("Order") = 'trichoptera' THEN 8
> WHEN lower("Order") = 'ephemeroptera' THEN 6
> WHEN lower("Order") = 'plecoptera' THEN 7
> WHEN lower("Class") = 'oligochaeta' THEN 1
> WHEN lower("Family") LIKE 'chiron%' THEN 2
> WHEN lower("Order") = 'diptera' AND lower("Family") NOT
> LIKE 'chiron%' THEN 3
> ELSE 4
> END AS "Ranking",
> CASE
> WHEN lower("Order") = 'coleoptera' THEN 'Cole
[SQL] References NULL field
This is PostgreSQL 7.4 (long story). I have a table, registrars, which
has, among other things,
note_id integer references notes
The notes table has, among other things,
note_id integer default nextval('notes_seq')
Right now, there's nothing in the notes table, and all the records in
the registrars table have null for their note_id field.
I want to do a query (in PHP, FWIW) that returns all the fields in
registrars, and the contents of any relevant notes records, or null for
those fields if there is no corresponding record in the notes table. But
when I do
SELECT * FROM registrars, notes WHERE regname = 'blah'
no matter that I put after the 'blah' (or nothing), I get no results. As
soon as I add notes into the tables being queried, I get nothing.
There's some special way I need to shape this query, but I don't know
what it is. Any help would be appreciated.
--
Paul M. Foster
---(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
Re: [SQL] References NULL field
On Sun, Apr 02, 2006 at 11:43:34PM -0400, Paul M Foster wrote: > I want to do a query (in PHP, FWIW) that returns all the fields in > registrars, and the contents of any relevant notes records, or null for > those fields if there is no corresponding record in the notes table. But > when I do > > SELECT * FROM registrars, notes WHERE regname = 'blah' > > no matter that I put after the 'blah' (or nothing), I get no results. As > soon as I add notes into the tables being queried, I get nothing. The above query does a cross (cartesian) join of registrars and notes. Since notes is empty the join result is empty. Try an outer join: SELECT * FROM registrars LEFT OUTER JOIN notes USING (note_id) WHERE regname = 'blah'; -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
