Re: Trouble with an outer join

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(at)ethereal-realms(dot)org>
To: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: "PostgreSQL Novice List" <pgsql-novice(at)postgresql(dot)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(at)ethereal-realms(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>