Re: JOIN - Mailing list pgsql-general

* Grant Furick <furick1@attglobal.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@archonet.com>
> To: "Grant Furick" <furick1@attglobal.net>
> Cc: <pgsql-general@postgresql.org>
> Sent: Thursday, March 22, 2001 4:15 PM
> Subject: Re: [GENERAL] JOIN
>
>
> On 3/21/01, 9:16:05 PM, Grant Furick <furick1@attglobal.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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749