Re: Trouble with an outer join - Mailing list pgsql-novice

On Sun, 25 Sep 2005, Martin Foster wrote:

> I want to run an outer join query on two tables so that the tags from
> one will always appear even if the other table is lacking those
> attributes.     Unfortunately, while the join seems to work there is no
> filtering taking place, causing a certain amount of strife.
>
> The two tables are as follows:
>
>             Table "ethereal.tag"
>   Column  |         Type          |                  Modifiers
> ---------+-----------------------+----------------------------------------------
>   tagname | character varying(25) | not null
>   tagtype | character varying(15) | not null default 'system'
>   tagdata | text                  |
> Indexes:
>      "pktag" PRIMARY KEY, btree (tagname, tagtype)
>
>             Table "ethereal.realmdesign"
>      Column     |         Type          | Modifiers
> ---------------+-----------------------+-----------
>   realmname     | character varying(30) | not null
>   tagname       | character varying(20) | not null
>   designcontent | text                  |
> Indexes:
>      "pkrealmdesign" PRIMARY KEY, btree (realmname, tagname)
>
>
> Obviously 'TagName' is the field which matches both.   These tables are
> not linked together and the removal of an entry from Tag will cause the
> removal from RealmDesign through code.
>
> The query is as follows.  Note that there is a lot of filtering as to
> prevent too many rows which are not required from being dealt with:
>
> SELECT
>    t.TagName       AS "TagName",
>    t.TagType       AS "TagType",
>    d.RealmName     AS "RealmName"
> FROM ethereal.Tag t
> LEFT OUTER JOIN ethereal.RealmDesign d
>   ON (t.TagName=d.TagName
> AND t.TagType='template'
> AND (t.TagName LIKE 'Realm%'
>   OR  t.TagName LIKE 'Offline%')
> AND d.RealmName='Horror')
> ORDER BY t.TagName;

...

>  From the output its pretty clear that the first 10 should have been
> omitted for more then one reason.   However they appear every time and
> in order to compensate for this, I have the script skip through unneeded
> entries manually.
>
> So what exactly am I doing wrong?

AFAIK, conditions like t.TagType='template' in the ON condition of an
outer join are not going to constrain the rows from t that are created but
instead constrain whether or not a row from d in considered as valid (ie,
you're saying to extend with NULLs for TagTypes other than 'template').

I think some of those conditions you want in a WHERE clause, possibly all
the ones that refer only to t.