Re: 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