Well, this is somewhat of a follow up to my previous post regarding self joins. Now what I'm hoping to do is "virtually" combine the results from two different record sets into one apparent record set.
Here is the skeleton of my application's data structure. There is a table called "folders" and a table called "files". They look like: | files | folders ============= ============ x| fileid x| folderid | filename | foldername | folderid | parentid | dsply_order | dsply_order files.folderid is fk to folders.folderid, folders.parentid is field for self joining to folderid. Now my difficulty is that I want to see results for the two tables together. As you know, folders can co-exist with files inside of a folder. Therefore if I know my current folder is 23, then I'd like to SELECT * FROM files WHERE files.folderid = 23 AND SELECT * FROM folders WHERE folders.parentid = 23 and then take the combined record sets and ORDER BY dsply_order. The only way that I know how to do this is to do two queries and put the results into an array and sort it outside of postgres. However my goal is to make life simpler for the application developers and just give them a view that has the data they need. As a side note, I'd probably need to add a field that would indicate 1 if the file came from files otherwise count(folders.*) WHERE parentid = folderid so that I can see if the folder is empty. As another side note, this operation will be performed quite frequently and should be fast. As I think about it, it seems that the only logical way would be to do this at the application level, not inside postgres. Please correct me if I'm wrong. -- Matthew Nuzum www.bearfruit.org [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
