SQL Query

From: Cliff <rcbuetikofer_at_verizon_dot.net>
Date: Mon, 29 Nov 2010 21:05:33 -0500
Message-ID: <2010112921053375946-rcbuetikoferatverizondotnet_at_news.giganews.com>



[Quoted] Hey all,

[Quoted] Must be Monday because I can't get my brain around this one.

 I'm trying to extract a unique column from a parent table (tab1) having the number of child records (tab2) exceed a certain count. I used a count of 2 in my example and so far I've got the following:

select unique a.rec_num,count(a.rec_size) from tab2 a,
having count(a.rec_size) >2
group by a.rec_num

See Data section at bottom:

My output was correctly:
1,3
3,3

Now that I know the rec_nums (1,3) that have > 2 count records and their count values. How do I find within each of the 3 records of rec_num=1 and rec_num=3 that have a rec_size the same that are equal? Essentially, I want to find duplicate records of rec_num and rec_size but ONLY where the count is > 2.

So my answer should be:
3,20

  • as this satisfies both criteria of having a count >2 within each rec_num and rec_size values that are equal.

Am I missing something obvious? Sorry if this is trivial for the experts but my SQL writing days ended with Oracle 7.

Thanks,
Cliff

-----------------------------------------------------------------{DATA}--------------------------------------

table:
tab1
column:

rec_num



1
2
3

table: tab2

[Quoted] column:  rec_num
column:  rec_size
________________
1,20
1,30
1,40
2,20
3,20

3,30
3,20 Received on Tue Nov 30 2010 - 03:05:33 CET

Original text of this message