count statement that I think needs having in it 2

[kernal]

I hope I'm giving you all the information needed:

2 Tables:

table 1

ID SUBJECT CRSE_NUMBER SECTION
1 TEST 500 1
1 PRP 600 3

2 TEST 100 1

3 PRP 200 4
3 BT 500 1
3 SD 200 5

Joined with table 2 on subject

SUBJECT ORG

TEST TEST
PRP PRP
BT BTL
SD SDY

I need to find all IDs that have more then 1 ORG so it would retrieve

ID SUBJECT CRSE_NUMBER SECTION
1 TEST 500 1
1 PRP 600 3

3 PRP 3200 4
3 BT 4500 1
3 SD 4200 5

Help is appreciated.

 

[SantaMufasa]

There are many ways to resolve your need. Here is one of them:

select id, subject,crse_number,section
  from tab1 w
 where exists (select 'x'
                 from (select id
                         from (select id
                                 from tab1 x,tab2 y
                                where x.subject = y.subject
                                group by id,org)
                        group by id
                       having count(*) > 1) z
                where w.id = z.id);

ID SUBJ CRSE_NUMBER    SECTION
-- ---- ----------- ----------
 1 TEST         500          1
 1 PRP          600          3
 3 PRP          200          4
 3 BT           500          1
 3 SD           200          5

5 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

select * from
(select x.id, 
       x.subject, 
       x.crse_number, 
       x.section, 
       count(distinct x.subject) over (partition by x.id) as counter
  from table1 x,table2 y
where x.subject = y.subject)
where counter>1

 

[kernal]

Thanks Dave and Dagon. I'll post another message when I have time to try both statements.