Re: SQL: When would I want to join a table with itself ?

"jcd" <newsgroup-responses-1_at_waycoolgear.com> wrote in news:bh8cpo$10r$1 @slb0.atl.mindspring.net:

> SQL: When would I want to join a table with itself ? Example please ?
> Thanks for any help!
>
> --
> (Only newsgroup replies will be answered. All others will be deleted.)
> http://www.WayCoolGear.com
>
>

When you want to compare one row in the table with another.

For example I have a table that records the used bytes for each tablespace as of 1am every morning. If I want to see how much a particular tablespace grew in a 24 hour period I might do something like this.

select
  yesterday.tablespace_name
, today.bytes - yesterday.bytes as daily_growth from
  tablespace_size today
, tablespace_size yesterday
where

    trunc(today.tablespace_date) = trunc(sysdate) and trunc(yesterday.tablespace_date) = trunc(sysdate)-1 and today.tablespace_name = yesterday.tablespace_name /

HTH Received on Mon Aug 11 2003 - 11:49:07 CDT