Re: Help!!! Complex SQL (For Pros Only)

From: <sybrandb_at_my-deja.com>
Date: Mon, 21 Jun 1999 10:00:37 GMT
Message-ID: <7kl2g2$6dg$1@nnrp1.deja.com>

In article <7kl149$60o$1_at_nnrp1.deja.com>, bonanos_at_yahoo.com wrote:
> Hi
>
> I have three tables.
>
> 1. [HEADER] is the master table
> ---------------------------------------------------------
> id_code -- Unique identifier
> cat_code -- Category code that this header falls under
> descr -- Header text
>
> 2. [CONTENT] is a detail table (many to [HEADER])
> ---------------------------------------------------------
> id -- Unique identifier
> title -- title of the document
> cdate -- Date document was added
> Author -- Author of the document
> cfile -- The name of the file
> ftype -- The type of file (html, audio,jpeg etc)
> Header_Code -- link to id_code in [Header table]
> cat_code -- Category this document is in.
>
> 3. [LINKS] is a detail table (many to [HEADER])
> ---------------------------------------------------------
> id -- Unique identifier
> cat -- Category this document is in.
> l_name -- the text of the links
> l_url -- the actual http:// address
> Summary -- a description
> Header_Code -- link to id_code in [Header table]
>
> What I want to produce is the following query result
>
> Header_code Description content_count link_count
> ------------------------------------------------------------------
> 123 Agriculture Policy 12 3
> 236 Agriculture Projects 3 3
> 123 Agriculture Budgets 5 0
>
> You get the piicture, let me express it verbally.
>
> I want all the Headers in a category with a count of documents and
link
> for each header.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Using inline views you could do something like the following select id header_code

     , descr
     , nvl(c.content_count,0)
     , nvl(l.link_count,0)

from header h
, (select header_code, count(*) content_count

from content
group by header_code) c
, (select header_code, count(*) link_count

from links
group by header_code) l
where c.header_code(+) = h.id
and l.header_code(+) = h.id

Hth
--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jun 21 1999 - 05:00:37 CDT