Re: SQL joins ?

In article <4de2q9$ck_at_ixnews8.ix.netcom.com>, chuckh_at_ix.netcom.com (Chuck Hamilton) writes:
|> vgoel_at_pts.mot.com (Vikram Goel) wrote:
|> >Peter,
 

|> >No need to do a join, try the following select:
 

|> > select substr(a.file_name,1,15) file_name,
|> > substr(a.tablespace_name,1,10) table_space_name,
|> > sum(a.bytes) total,sum(b.bytes) free
|> > from dba_data_files a, dba_free_space b
|> > where a.tablespace_name = b.tablespace_name
|> > group by a.file_name,a.tablespace_name:
|>
|> I don't think this will give the desired result because of how SQL
|> normalizes results in a join. For example if the left table has only 1
|> row and the right table has 2, the rows selected from the left table
|> will be repeated twice. This will throw off the sum() for any columns
|> from the left table. (Am I explaining this clearly? I'm not sure.) The
|> above SQL would only give the correct results if there was 1 free
|> space per file.
|>
|> What you really need to do is a join the dba_data_files table with a
|> dynamic view of the freespace table, where the aggregation (summing)
|> of dba_free_space is done *before* the join. Something like this
|> should work:
|>
|> select file_name
|> , tablespace_name
|> , bytes total
|> , free
|> from sys.dba_data_files a
|> , (select file_id, sum(bytes) free
|> from sys.dba_free_space
|> group by file_id) b
|> where a.file_id = b.file_id;
|> --
|> Chuck Hamilton
|> chuckh_at_ix.netcom.com
|>
|> Never share a foxhole with anyone braver than yourself!
|>

Here is an answer for your problem. And it works !

column file_id format 99 heading 'ID';
column file_name format a40 heading 'FILE-NAME'; column tablespace_name format a11 heading 'TABLE-SPACE';

select d.file_id, d.file_name, d.tablespace_name, d.bytes "Total", sum(f.bytes) "Free" from dba_data_files d, dba_free_space f
where d.file_id = f.file_id
group by d.file_id, d.file_name, d.tablespace_name, d.bytes; Received on Tue Jan 16 1996 - 00:00:00 CET