On Wed, 15 Jan 2003, Matthew Nuzum wrote: > 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.
Fortunately we have the set functions, specifically UNION ALL in this case. > 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. > > 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. Maybe something like (minus the number of files/empty part): CREATE VIEW viewname AS SELECT fileid, filename, folderid, dsply_order FROM files UNION ALL SELECT folderid, foldername, parentid, dsply_order FROM folders; SELECT * from viewname where folderid=23 order by dsply_order; Depending on whether you want a count of files or just an empty or not, and whether you want info on whether a particular entry is a file or folder, you'll probably need to add to the above. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
