Thread: show data from two tables together

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

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 ASSELECT fileid, filename, folderid, dsply_order FROM files UNION ALLSELECT 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.

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;