From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: show data from two tables together
Date: Wed, 15 Jan 2003 20:01:30 -0500
Message-ID: <003d01c2bcfa$d2084b40$6700a8c0@mattspc> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org
Subject: Re: show data from two tables together
In-Reply-To: <003d01c2bcfa$d2084b40$6700a8c0@mattspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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