From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: <pgsql-sql@postgresql.org>
Subject: join gone awry
Date: Fri, 21 Feb 2003 17:33:20 -0500
Organization: Bearfruit.org
Message-ID: <009501c2d9f9$3c5c9620$6900a8c0@mattspc>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal

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