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

Stephan Szabo wrote:

>> 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.
>

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)
  WHERE t.TagType='template'
  AND (t.TagName LIKE 'Realm%'
    OR  t.TagName LIKE 'Offline%')
  AND d.RealmName='Horror'
  ORDER BY t.TagName;

Let's try that change which oddly enough does not do an outer join at
all.   Here is the sample output:

        TagName        | TagType  | RealmName
----------------------+----------+-----------
  RealmDice            | template | Horror
  RealmFrameAutoscroll | template | Horror
  RealmFrameNormal     | template | Horror
  RealmHeader          | template | Horror
  RealmNotice          | template | Horror
  RealmPanel           | template | Horror
  RealmPrivate         | template | Horror
  RealmRemote          | template | Horror
  RealmSeperator       | template | Horror
  RealmWarning         | template | Horror
  RealmZoom            | template | Horror

Now let's try a variation:

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 d.RealmName='Horror')
  WHERE t.TagType='template'
  AND (t.TagName LIKE 'Realm%'
    OR  t.TagName LIKE 'Offline%')
  ORDER BY t.TagName;

Which allows us to get what we need.   Which gets rather confusing as to
how to get a join to work exactly like people expect it too.

        TagName        | TagType  | RealmName
----------------------+----------+-----------
  OfflineInfo          | template |
  OfflinePage          | template |
  OfflinePanel         | template |
  OfflineWarning       | template |
  RealmBanner          | template |
  RealmDice            | template | Horror
  RealmFooter          | template |
  RealmFrameAutoscroll | template | Horror
  RealmFrameNormal     | template | Horror
  RealmHeader          | template | Horror
  RealmInfo            | template |
  RealmJavascript      | template |
  RealmNotice          | template | Horror
  RealmPanel           | template | Horror
  RealmPrivate         | template | Horror
  RealmRefresh         | template |
  RealmRemote          | template | Horror
  RealmSeperator       | template | Horror
  RealmSupp            | template |
  RealmWarning         | template | Horror
  RealmZoom            | template | Horror
(21 rows)

Anyone know good documentation on how to determine exactly where to cram
thing as necesssary?

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org