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> |
Subject: | Re: Trouble with an outer join |
Date: | 2005-09-26 00:30:28 |
Message-ID: | 43374124.7000000@ethereal-realms.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)ethereal-realms(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | me | 2005-09-26 00:35:10 | Re: Trouble with an outer join |
Previous Message | Stephan Szabo | 2005-09-26 00:12:53 | Re: Trouble with an outer join |