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

this should work just fine:

 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' or d.RealmName IS NULL)
  ORDER BY t.TagName;

cheers,
thomas

----- Original Message -----
From: "Martin Foster" <martin@ethereal-realms.org>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: "PostgreSQL Novice List" <pgsql-novice@postgresql.org>
Sent: Monday, September 26, 2005 2:30 AM
Subject: Re: [NOVICE] Trouble with an outer join


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>