On Fri, Sep 16, 2005 at 12:06:19PM -0700, Roger Hand wrote:
> > select * from instructor_counts where class_time > now();
> >
> > But class_time is not part of the VIEW so that's not valid.
>
> No problem, just make it a part of the view. See the classes section below.
>
> CREATE VIEW future_instructor_counts
> AS
> SELECT * FROM
>
> (SELECT
> person.id AS person_id,
> first_name,
> last_name) personinfo
>
> INNER JOIN
>
> -- Add class_time field!
> (SELECT class.id, class_time FROM class
> WHERE class_time > now() ) classes
>
> INNER JOIN
>
> (SELECT
> id, count(class) AS class_count
> FROM instructors GROUP BY id) classcount
>
> ON personinfo.person_id = instructors.id
> AND classes.id = instructors.id
I couldn't get that to work -- Postgresql isn't that helpful just
reporting "ERROR: syntax error at or near ";" at character 496" even
after adding a FROM in the first select. So, I'm stabbing in the dark
to get it to work.
> [Disclaimer: I've not tested this code at all. It could help if you sent table definitions and maybe even dummy
> data via insert commands.]
Ok -- this should be cut-n-paste:
CREATE TABLE class (
id integer PRIMARY KEY,
class_time timestamp(0) with time zone,
name text
);
CREATE TABLE person (
id integer PRIMARY KEY,
first_name text
);
create table instructors (
person integer NOT NULL REFERENCES person,
class integer NOT NULL REFERENCES class,
PRIMARY KEY (person, class)
);
INSERT INTO person (id,first_name) values (1,'Joe');
INSERT INTO person (id,first_name) values (2,'Mary');
INSERT INTO person (id,first_name) values (3,'Bob');
INSERT INTO person (id,first_name) values (4,'Cindy');
INSERT INTO class (id,name, class_time) values (1,'Math', now());
INSERT INTO class (id,name, class_time) values (2,'Math', now() + interval '1 day');
INSERT INTO class (id,name, class_time) values (3,'Science', now());
INSERT INTO class (id,name, class_time) values (4,'PE', now() + interval '1 day');
INSERT INTO instructors (person, class) values (1,1); -- joe teaches math now
INSERT INTO instructors (person, class) values (1,2); -- joe teaches math tomorrow
INSERT INTO instructors (person, class) values (2,2); -- with Mary
INSERT INTO instructors (person, class) values (3,3); -- Bob teaches science now
INSERT INTO instructors (person, class) values (4,3); -- Cindy teaches science tomorrow
-- view
CREATE VIEW instructor_counts
AS
SELECT person.id AS person_id,
first_name,
count(instructors.class) AS class_count
FROM class, instructors, person
WHERE class.id = instructors.class AND
person.id = instructors.person
-- AND class_time > now()
GROUP BY person_id, first_name;
select * from instructor_counts order by class_count desc;
-- Returns:
person_id | first_name | class_count
-----------+------------+-------------
1 | Joe | 2
2 | Mary | 1
3 | Bob | 1
4 | Cindy | 1
(4 rows)
My GOAL above is to be able to add a WHERE class_time > $some_time.
Here's were I left off, which I never could get to work.
The individual selects work, but seems like I need to be say
c.class_id = i.class in addition. But I can't even get
this without syntax errors:
CREATE VIEW instructor_counts
AS
SELECT *
FROM
(SELECT person.id AS person_id, first_name
FROM person) p
INNER JOIN
(SELECT class.id AS class_id, class_time
FROM class) c
INNER JOIN
(SELECT person, count(class) AS class_count
FROM instructors GROUP BY person) i
ON ( p.person_id = i.person);
That also looks like the selects are going to be full table scans.
--
Bill Moseley
moseley@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
