Re: JOIN - Mailing list pgsql-general

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?

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