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.readonlyAS 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
