Re: faster output from php and postgres - Mailing list pgsql-php

So, I have the same problem, but I need all authors for each publication
to show up in it's own column.  I tried the full join query from a
suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
of ALL authors at once.

I'll do some RTFMing of the joins.. and post any results I get

BUT, right now, looping over each publication with php isn't taking that
long.  I would like to know how to make this query, though!

Please let me know how to get a result like:
____________________________________________________
|All Authors|Title|Source|Year|Type|Length|Keywords|
----------------------------------------------------

If there is a way on the SQL side to do this, that is ;>

Here we go:

CREATE TABLE "author" (
        "auth_id" integer DEFAULT
nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
        "first" text,
        "last" text,
        "auth_last_updated" timestamp with time zone,
        Constraint "author_temp_pkey" Primary Key ("auth_id")
);


CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);


CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
(auth_last_updated);



CREATE TABLE "publication" (
        "copyis" text,
        "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
NOT NULL,
        "title" text,
        "source" text,
        "year" text,
        "month" text,
        "length" text,
        "type" text,
        "keywords" text,
        "copyright" text,
        "abstract" text,
        "pdformat" text,
        "pub_last_updated" timestamp with time zone
);



CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
(pub_id);


CREATE INDEX keywords_publication_key ON publication USING btree
(keywords);


CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
btree (pub_last_updated);


CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
(pub_id);



CREATE TABLE "pub_auth" (
        "pub_auth_id" integer DEFAULT
nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
        "pub_id" integer,
        "auth_id" integer,
        Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
);



CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);


CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);


On Sat, 24 May 2003, Frank Bax wrote:

> Finding previous examples of complex joins in archives is not likely an
> easy thing to find.
>
> pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
>
> Change [database] to the name of your database - this command will dump out
> schema relative to your request.  Post the results to this list.  Then ask
> us the question "how do I write a SELECT that produces...[ you finish this
> sentence]".  Question probably more appropriate to the list you mentioned,
> but I expect there are people here who are just as capable of answering the
> question.  I've even seen examples where the process goes through several
> emails before SQL produces desired results exactly.
>
>  >How would a join make this easier?
>
> I have always found that one properly constructed complex query is always
> "cheaper" in runtime than numerous queries inside a foreach loop.  Your
> final query will likely include joining a table to itself (this can
> sometimes be a difficult concept to grasp).
>
> Frank
>
>
> At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
>
>
> >I'm glad this came up, because I have the same type of problem.  Except,
> >I don't see how a join can work... of course, I'm not really schooled in
> >this stuff.
> >
> >I also have three tables: author, publication, and pub_auth.
> >
> >There are multiple authors for some publications, so it is necessary to
> >check each publication selected for ALL authors.  I'm doing this with a
> >foreach loop on the result of each publication key returned.
> >
> >How would a join make this easier?
> >
> >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
> >not sure what to search for...
> >


-Chadwick