Re: Trouble with an outer join

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.