Re: JOIN

* Grant Furick <furick1(at)attglobal(dot)net> [010325 19:40]:
> Richard Thanks for the help. I now get a "OUTER JOIN is not yet supported"
> from the Postgres DB though. Are you using this with postgres successfully?
OUTER JOIN is in V7.1

LER

>
> Grant
> ----- Original Message -----
> From: "Richard H" <dev(at)archonet(dot)com>
> To: "Grant Furick" <furick1(at)attglobal(dot)net>
> Cc: <pgsql-general(at)postgresql(dot)org>
> Sent: Thursday, March 22, 2001 4:15 PM
> Subject: Re: [GENERAL] JOIN
>
>
> On 3/21/01, 9:16:05 PM, Grant Furick <furick1(at)attglobal(dot)net> wrote
> regarding [GENERAL] JOIN:
>
> Sorry about the delay, Grant - I think everyone was scared off by the
> number of joins in your query:-)
>
> > I am trying to output news. An article can have an image or not
> sometimes.
> > Can someone help me get this to work in Postgres?
>
> Yep - outer join is what you want.
>
> > select a.article_id, a.title, a.url, a.synopsis, a.publish_date,
> > c.parent_category_id, c.category_id, c.category_name,
> > i.server_image_name
> > from ((article a JOIN article_category_assoc acs ON a.article_id =
> > acs.article_id)
> > JOIN category c ON c.category_id = acs.category_id)
> > LEFT OUTER JOIN (image i JOIN article_image_assoc aia ON i.image_id =
> > aia.image_id)
> > ON a.article_id = aia.article_id
> > where i.image_type_id = 1
> > and a.live_date <= #CreateODBCDate(Now())#
> > and a.active_ind = TRUE
> > and a.status_id = 2
> > and c.category_id=#intCategoryID#
>
>
> I think I've boiled this down correctly:
>
> create table article (article_id int4, title text);
> create table article_category_assoc (article_id int4, category_id int4);
> create table category (category_id int4, category_name text);
> create table article_image_assoc (artice_id int4, image_id int4);
> create table image (image_id int4, server_image_name text);
>
> SELECT a.article_id, a.title, c.category_id, c.category_name, i.image_id
> FROM
> (article a JOIN article_category_assoc acs ON a.article_id=acs.article_id
> JOIN category c ON c.category_id=acs.category_id )
> LEFT JOIN article_image_assoc aia ON a.article_id = aia.article_id
> LEFT JOIN image i ON i.image_id = aia.image_id;
>
> article_id | title | category_id | category_name | image_id
> ------------+-----------+-------------+---------------+----------
> 1 | article 1 | 1 | category 1 | 1
> 2 | article 2 | 1 | category 1 |
> (2 rows)
>
> I think the trick is the two LEFT JOINs rather than one. You might find
> it easier to make a couple of views for the various bits and then join
> the views:
>
> create view article_view as
> select a.article_id, a.title, c.category_id, c.category_name from
> (article a JOIN article_category_assoc acs on a.article_id=acs.article_id
> JOIN category c on c.category_id=acs.category_id );
>
> create view image_view as
> select aia.article_id,i.image_id from article_image_assoc aia join image
> i on i.image_id = aia.image_id;
>
> select av.*, iv.* from article_view av left join image_view iv on
> av.article_id=iv.article_id;
>
> - Richard Huxton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749