Oracle, resultset should return only counts of more than 1

I'm trying to return only rows for people that have a count of more than 1 row with matching criteria. My problem is if I use the 'having count' I cannot display columns that i want to see.
Sample data is below. I do not want to see 123456 data as this person does not have more than 1 row matching criteria:
SQL> select a.person_id, b.class_code, a.school_code, a.end.date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('123456','1234567')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and a.end_date > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  order by 1,2,3,4
 11  /

PERSON_ID     CLASS_CODE       SCHOOL   END_DATE
------------- ---------------- -------- ------------------------
123456    RMA120B          804      26-JUN-09
123456   RMOFFICEB        804      26-JUN-09
1234567   RMA125B          804      26-JUN-09
select a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a, school_classes b
where a.school_code = b.school_code
and a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and a.school_year = b.school_year
and a.class_code = b.class_code
and b.class_homeroom_flag = 'p'
and a.person_id in (select t.person_id
                                               from student_program_class_tracks t
                                               where  t.person_id = a.person_id
                                               and t.school_code = a.school_code
                                               and t.school_year = a.school_year
                                               and t.school_year = '20082009'
                                               and t.end_date > '01-JUN-2009'
                                               group by t.person_id
                                               having count(person_id) > 1)

Open in new window

guinnieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Assuming personid being the unique identifier...

select distinct A.* from yourtable A join
(select personid, count(personid) from yourtable group by personid having count(personid) > 1) B on A.personid = B.personid
0
guinnieAuthor Commented:
Racimo:

  Yes, that works if you only need to see person_id. I need to dsiplay class_code , school_code and school _year also. That's where the difficulty is. For example, I can't use:

group by person_id, class_code, school_code, school_year. If I put those columns in the group by statement, I get no rows returned.

I need to know how to include these columns with the group and having statement.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Same idea...HTH

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

guinnieAuthor Commented:
Racimo:
     That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code.
SQL> desc school_classes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHOOL_CODE                               NOT NULL VARCHAR2(8)
 SCHOOL_YEAR                               NOT NULL VARCHAR2(8)
 CLASS_CODE                                NOT NULL VARCHAR2(16)


 
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
In that case...

<< That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code. >>
select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.school_code = c.school_code
                  and a.school_year = c.school_year
                  and a.class_code = c.class_code
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I realize I am having some difficulties understanding what you are trying to accomplish.  What is the unique identifier of the student_program_class_tracks table?  Also please try my first proposal...
0
guinnieAuthor Commented:
student_program_class_tracks unique identifier is person_id. However, student_program_class_tracks
also has class_code, school_year, school_code which matches to school_classes. But
school classes does not have person_id column.
The 'first proposal' does not work because person_id does not exist in school_classes.


0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<The 'first proposal' does not work because person_id does not exist in school_classes.>>
It's ok...If personid is the unique identifier of student_program_class_tracks then the join on the columns you mentionned does not seem necessary...

Could you try the query thanks...
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
to clarify school classes will return all necessary results...
0
guinnieAuthor Commented:
I have pasted result of query below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from school_classes group by pe
rson_id having count(distinct person_id) > 1) c
  8                    on a.school_code = c.school_code
  9                    and a.school_year = c.school_year
 10                    and a.class_code = c.class_code
 11  where a.school_year = '20082009'
 12  and a.end_date > '01-JUN-2009'
 13  and b.class_homeroom_flag = 'p'
 14  /
        inner join (select person_id, count(distinct person_id) from school_classes group by person_id havi
                                                                                                   
ERROR at line 7:
ORA-00904: "PERSON_ID": invalid identifier

0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry please try..

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
guinnieAuthor Commented:
I think you are close. The only reason we go to school classes is to see if class_code = 'P' . For some reason though it returns no rows. I have paste data to show you what I expect to see. We should see
rows for person_id 0001183676.
PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
This is what I expect to see.
The result of you query is below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from student_program_class_trac
ks group by person_id having count(distinct person_id) > 1) c
  8                    on a.person_id = c. person_id
  9  where a.school_year = '20082009'
 10  and a.end_date > '01-JUN-2009'
 11  and b.class_homeroom_flag = 'p'
 12  /

no rows selected

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3,4
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     CHA3U1-01        804      20082009 26-JUN-09
000137546     HRT3M0-06        804      20082009 26-JUN-09
000137546     MCR3U1-04        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09
000137546     SPH3U1-03        804      20082009 26-JUN-09

11 rows selected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Maybe the collation is case sensitive...

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'P'
0
guinnieAuthor Commented:
Tried that too without success. Below is a query, to show data for the 2 people. I only want to dsiplay
person_id, b.class_code, a.school_code, a.school_year for those people who have more than 1 end date being > 01-JUN-2009. But I can't figure it out. The row for person_id 000137546 should not be showing up in final sql.
SQL> select a.person_id, b.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and to_date(a.end_date) > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09

0
awking00Information Technology SpecialistCommented:
See attached.
query.txt
0
awking00Information Technology SpecialistCommented:
On further review, I need to remove the class_code (and perhaps the school_code) from the partition clause.
0
guinnieAuthor Commented:
Hello Awking:

Thank you for your suggestion. I have ran the query but get error as seen in attached file.
awking-reply.txt
0
awking00Information Technology SpecialistCommented:
On even further review, I think it just needs to be partitoned by person_id.
0
awking00Information Technology SpecialistCommented:
Can you post the table structures for the student_program_class_tracks and school_classes tables and provide some sample data for them?
0
awking00Information Technology SpecialistCommented:
There is a typo, "a.end.date" should be "a.end_date" on the second line.
0
guinnieAuthor Commented:
Awking00:

I think you almost have it. The only thing now is I see 1 row returned when I should see both rows.
See attached file. This will show the results of your query and the query that I show the data.
I need to see both rows for person_id 0001186376.
Thank you
awking-reply2.txt
0
awking00Information Technology SpecialistCommented:
Change "(partition by a.person_id, b.class_code, a.school_code, a.end_date order by rownum)" cnt to "(partition by a.person_id order by rownum) cnt" and see what you get.
0
guinnieAuthor Commented:
AWKING00:

That works great. Just one more thing if you don't mind. When I take out test for just 2 person_id, I get too many rows. I only want to get persons with multiple class codes with that end_date. i.e.
I removed check for a.person_id in ('123456','1234567'). Should I put in another inner join to school_program_class_tracks ?
0
awking00Information Technology SpecialistCommented:
I'm not sure what you mean. Can you post the query without the 2 person_id check, what the results were, and what you expected them to be?
0
guinnieAuthor Commented:
Hello Awking:

Sorry for the delay. I will attachequery and results (only parital as the resultset is quite large).
The results are not query with this query. It is returning people with only 1 homeroom. Not sure why .
Hope you can help ?
The 2nd file shows example of people who should not be in resultset.


awking-july14-1.txt
awking-july14-2.txt
0
awking00Information Technology SpecialistCommented:
See attached.
query.txt
0
guinnieAuthor Commented:
Awking00:

Thanks for sticking with this issue. Results are attached.

Also, I will be leaving for the day shortly. But I will be on tonight to respond.
awking-july-15.txt
0
awking00Information Technology SpecialistCommented:
Can you post the relevant data that exists in the student_program_class_tracks and school_classes tables that provided those latest results?
0
guinnieAuthor Commented:
Awking000:

I shall give you a sample. For the data below, only the person id of 0001183676 with class code beginning with RM should be returned. The others do not have more than 1 homeroom.

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('0001100193','0001100019','0001100020','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001100019    4H               859      20082009 26-JUN-09
0001100020    3K               853      20082009 26-JUN-09
0001100193    5C               829      20082009 26-JUN-09
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09

9 rows selected.
0
awking00Information Technology SpecialistCommented:
Okay, now I'm a little confused. When you ran the last query, we got results like the following:
0001100019    4H               859      26-JUN-09          1
0001100019    4H               859      26-JUN-09          2
0001100020    3K               853      26-JUN-09          1
0001100020    3K               853      26-JUN-09          2
0001100193    5C               829      26-JUN-09          1
0001100193    5C               829      26-JUN-09          2
0001100193    5C               829      26-JUN-09          3
0001100193    5C               829      26-JUN-09          4
Indicating that there were duplicate values for the four attributes, but now you're not showing them.
Also, what do you mean by only the 0001186376 recode with class_code beginning with RM should be returned? If the values were just
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
without the RMOFFICEB record, should the id be returned or not? If not, what is the criteria you would use to eliminate the other four records with class_codes that don't begin with RM?
0
guinnieAuthor Commented:
Awking00:

 I'm only showing examples because the result sets can be very large. What I'm trying to say is this:
Certain all person_ids will have multiple class_codes, it is only when we go to the school_classes
table and look for those class codes that are flagged as 'P' that we know that it is a homeroom.
In the case of person_id 0001186376, the class codes that are RMA120b and RMOFFICEB are flagged as 'P' in the school_classes so we want to see them in the resultset .
Does that clarify ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.