Re: 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