Thread: JOIN

JOIN

From

"Grant Furick"

Date:

22 March 2001, 10:58:02

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?

 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#

Thanks

Re: JOIN

From

Richard H

Date:

22 March 2001, 20:28:28

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

Re: JOIN

From

Richard Huxton

Date:

23 March 2001, 08:22:35

Grant Furick wrote:
>
> 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?

Don't know which version it's introduced in 7.0 or 7.1 - I'm running
7.1beta on my development rig. I think the first release candidate is
out shortly, so it's pretty stable already. I'd upgrade - the full
release shouldn't be far off.

- Richard Huxton

Re: JOIN

From

"Grant Furick"

Date:

25 March 2001, 23:33:54

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

Re: JOIN

From

Larry Rosenman

Date:

26 March 2001, 00:02:28

* 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