From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: join gone awry
Date: Fri, 21 Feb 2003 17:33:20 -0500
Message-ID: <009501c2d9f9$3c5c9620$6900a8c0@mattspc> (raw)
OK, probably another simple join problem. I'm updating an application and
it's data structure. I'm using MS Access to rearrange my data, so it's
going from PG 7.1 -> Access 97 -> Pg 7.3.
I've got one query that's just not working right and I can't explain why.
The table should have the same amount of rows before and after, but instead
it's going from about 3,000 rows to 11,000 rows.
Here's my query: (Yes, there's some VBA in it, sorry)
SELECT
new_pages_temp.accountid, new_pages_temp.pagetype,
new_pages_temp.path AS pagename,
IIf(Len(new_pages_temp.folder)>1, folders.folderid,0) AS folderid,
Now() AS created_date, Now() AS modified_date,
new_pages_temp.readonly AS read_only, new_pages_temp.display,
Null AS page_title, Null AS page_description, Null AS page_keywords,
Null AS robots_index, Null AS robots_follow, 1 AS author, 1 AS
charset,
Null AS templateid, Null AS expires, new_pages_temp.ftr_data AS
data,
new_pages_temp.dsplyorder AS dsply_order
INTO new_pages
FROM folders
RIGHT JOIN new_pages_temp
ON (folders.accountid = new_pages_temp.accountid)
AND (folders.foldername = new_pages_temp.folder);
You see, I used to store the full path to a page in the database, so it was
something like this: /myfolder/pagename and then a second field for folder
contained /myfolder/.
You can see that's very tedious. Now there's a separate table for folders
where it basically looks like this:
Folderid
Foldername
Etc.
So if there's a page called MyPage and it's in a folder, and the same
account has a total of 4 folders, the MyPage will show in the results 4
times, once listed in each folder.
I've also stripped the "/" off the beginning and end of the folder name and
the root folder isn't contained in the folders table, only sub folders are.
Any suggestions? I've tried getting rid of the right join and replace it
with "WHERE folders.foldername = new_pages_temp.foldder AND
folders.accountid = new_pages_temp.accountid" but I get the exact same
results.
Thanks for any help you might be able to give.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org
Subject: Re: join gone awry
In-Reply-To: <009501c2d9f9$3c5c9620$6900a8c0@mattspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox