Re: SQL question...

In article <01bd8fc2$4e81d1c0$6383209a_at_gill>, marketing_at_force12.com (Force12 Solutions Ltd) wrote:

> I realise this is more of an SQL question than an Oracle one but I'm
> doing
> it
> on Oracle and so any Oracle specific functions can be used. I have three
> tables:
>
> SITE; Site_ID (NUMBER), Site_Name (VARCHAR2)
>
> REPORT_BATCH; Batch_ID (NUMBER), Batch_Name (VARCHAR2)
>
> REPORT_BATCH_MEMBERSHIP; Site_ID (NUMBER), Batch_ID (NUMBER)
>
> Each REPORT_BATCH can contain many SITE's and each SITE can be in
> many REPORT_BATCH's. The REPORT_BATCH_MEMBERSHIP table contains
> a record for each batch a site is a member of. The query I need will
> return
> a list
> of all report batches (BATCH_ID, BATCH_NAME) and also an extra field
> that
> says
> how many members (SITE's) that batch has. I think I have the concept
> right
> - Using
> an OUTER JOIN, COUNT(*) and GROUP BY REPORT_BATCH.BATCH_ID but I can't
> seem to crack it. Could anyone PLEASE help as this is driving me up the
> wall!
> Thanks in advance, hope to hear from someone soon.....
>
> Alex Tait
> Force12 Solutions Ltd
> ajt_at_force12.com
>
>

Is this what you need?

SELECT rb.batch_id , rb.batch_name , count(rbm.site_id) FROM report_batch rb,

     report_batch_membership rbm
WHERE rb.batch_id = rbm.batch_id
GROUP BY 1,2
/

Luke Received on Sat Jun 06 1998 - 10:03:11 CDT