pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
show data from two tables together 3+ messages / 2 participants [nested] [flat]
* show data from two tables together @ 2003-01-16 01:01 Matthew Nuzum <cobalt@bearfruit.org> 0 siblings, 1 reply; 3+ messages in thread From: Matthew Nuzum @ 2003-01-16 01:01 UTC (permalink / raw) To: pgsql-sql 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 cobalt@bearfruit.org ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: show data from two tables together @ 2003-01-16 03:26 Stephan Szabo <sszabo@megazone23.bigpanda.com> parent: Matthew Nuzum <cobalt@bearfruit.org> 0 siblings, 1 reply; 3+ messages in thread From: Stephan Szabo @ 2003-01-16 03:26 UTC (permalink / raw) To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: pgsql-sql 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: show data from two tables together @ 2003-01-16 03:46 Matthew Nuzum <cobalt@bearfruit.org> parent: Stephan Szabo <sszabo@megazone23.bigpanda.com> 0 siblings, 0 replies; 3+ messages in thread From: Matthew Nuzum @ 2003-01-16 03:46 UTC (permalink / raw) To: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>; 'Matthew Nuzum' <cobalt@bearfruit.org>; +Cc: pgsql-sql Hmm... I've never used this before. I'll try it. Thanks for your help and your quick reply! -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org > Fortunately we have the set functions, specifically UNION ALL in this > case. > > 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; ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-16 03:46 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-01-16 01:01 show data from two tables together Matthew Nuzum <cobalt@bearfruit.org> 2003-01-16 03:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com> 2003-01-16 03:46 ` Matthew Nuzum <cobalt@bearfruit.org>
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