Help narrowing results?

I was under the impression that the Distinct clause in a select statement would only pull unique rows.  I'm pulling multiple rows of redundant info.  Should I use a group by instead??  I have atttached code and results of query.  Thanks for the help!  The results look like this:

3448        Afuape                         3476136434        Oluyemi         Samuel          NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              NULL
3448        Afuape                         3476136434        Oluyemi         Samuel          NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              7178651781
3448        Afuape                         3476136434        Oluyemi         Samuel          NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              7183703851
6467        Aguayo-Johnson                 7174430351        Erica           NULL            NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              NULL
6467        Aguayo-Johnson                 7174430351        Erica           NULL            NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              7175588099
4024        Altman                         7176452317        Barry           Eddington       NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              NULL
4024        Altman                         7176452317        Barry           Eddington       NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              7177325020
4024        Altman                         7176452317        Barry           Eddington       NULL                           NULL            NULL            NULL                 NULL                 NULL                 SP                 0708              7179869613
5962
SELECT Distinct name_master.id_num,   
 
         name_master.last_name,   
 
         name_master.mobile_phone,   
 
         name_master.first_name,   
 
         name_master.middle_name,   
 
         cm_emerg_contacts.emrg_last_nme,   
 
         cm_emerg_contacts.emrg_first_nme,   
 
         cm_emerg_contacts.emrg_middle_nme,   
 
         cm_emerg_contacts.emrg_home_phn,   
 
         cm_emerg_contacts.emrg_wrk_phn,   
 
         cm_emerg_contacts.emrg_mobl_phn,   
 
         student_master.most_recnt_trm_enr,   
 
         student_master.most_recnt_yr_enr,   
 
         address_master.phone  
 
    FROM student_master LEFT OUTER JOIN cm_emerg_contacts ON student_master.id_num = cm_emerg_contacts.id_num,   
 
         name_master,   
 
         address_master,   
 
         biograph_master  
 
   WHERE ( student_master.id_num = address_master.id_num ) and  
 
         ( student_master.id_num = biograph_master.id_num ) and  
 
         ( student_master.id_num = name_master.id_num ) and  
 
         ( ( student_master.most_recnt_yr_enr = '0708' ) AND  
 
         ( name_master.first_name is not null ) AND  
 
         ( student_master.most_recnt_trm_enr = 'SP' ) )
 
 
	ORDER BY name_master.last_name ASC,   
 
         cm_emerg_contacts.emrg_last_nme ASC

Open in new window

jasonbrandt3Asked:
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.

Chris MangusDatabase AdministratorCommented:
It's caused by your joining table.  I'd bet that some data elements you HAVEN'T chosen are not duplicates.
0
LowfatspreadCommented:
please indicate which rows you consider to be duplicates they look distinct to me!

isn't the phone always different?


 
0
Richard QuadlingSenior Software DeveloperCommented:
By the looks of it, the last column is different each time.

So NFF. No Fault Found.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Richard QuadlingSenior Software DeveloperCommented:
Sorry for the dup posting LFS.
0
jasonbrandt3Author Commented:
So the syntax is ok.  Maybe i can group on the name?
0
LowfatspreadCommented:
however your joins/code appear to be superflouse/inefficent

do you really need to join to the biography master?

and shouldn't the student_master just be an existence check?

please explain the table relationships ... (which are 1:1. 1:M)

try this...

ps NULL <> NULL
although with a group by they will be aggregated...
 
SELECT Distinct
       nm.id_num
      ,nm.last_name
      ,nm.mobile_phone
      ,nm.first_name
      ,nm.middle_name
      ,EC.emrg_last_nme
      ,EC.emrg_first_nme
      ,EC.emrg_middle_nme
      ,EC.emrg_home_phn
      ,EC.emrg_wrk_phn
      ,EC.emrg_mobl_phn
      ,'SP' as most_recnt_trm_enr
      ,'0708' as most_recnt_yr_enr
      ,am.phone  
 
    FROM (select * 
            from name_master
           where nm.first_name is not null     
         ) as nm 
   Inner join address_master as am 
      on nm.id_num = am.id_num 
    LEFT OUTER JOIN cm_emerg_contacts  as EC
      ON nm.id_num = EC.id_num
 
   Where Exists
          (select id_num 
            from student_master as sm
           Where most_recnt_yr_enr = '0708'
             AND most_recnt_trm_enr = 'SP'
             and sm.id_num=nm.id_num 
          ) as SM
 
   ORDER BY nm.last_name ASC
           ,EC.emrg_last_nme ASC

Open in new window

0
LowfatspreadCommented:
>So the syntax is ok.  Maybe i can group on the name?

NO the syntax is not ok for the problem you are attempting to solve...

howevber you need to explicitly tell us what the problem is that you need to solve...

e.g.
i want to obtain a listing of emergency contact numbers for students enrolled in the "current term" ... also detailing how your tables/system are joined / operate..

we aren't mind readers an cannot know how your system is dealing with student history for instance...



0
jasonbrandt3Author Commented:
I just want the name to list once, I understand since I'm using the Distinct statement I'm going to get multiple records based on (as earlier pointed out) the last column containing unique data.  I guess what I'm asking is can I group on the name and list the unique rows:
e.g.
Name
data  data
data data
NextName

Hope this makes my question clearer...
0
jasonbrandt3Author Commented:
After reading the code you posted, I think I'm definitely using more tables than I need, I'm just learning the layout of this database, so it was more not wanting to miss something.
0
LowfatspreadCommented:
no you can't just group on the name to list unique rows.....

your unique indicator is (for a start) is the id_num (how many John Smiths can you have in the school?)

if you want only 1 contact set of information per student then you have to specify which set (ie row of information you will select over the others that may be available...)

you could "group by"  but that would only get max or min values across the set of information available which WILL NOT BE CONSISTENT....

do you tables contain Date/time information indicating which is the most recent set of information?

the code ensures that only 1 emergency contact is selected
based on selecting the max(Name) and max(phone) for any contacts for the student...


SELECT Distinct
       nm.id_num
      ,nm.last_name
      ,nm.mobile_phone
      ,nm.first_name
      ,nm.middle_name
      ,EC.emrg_last_nme
      ,EC.emrg_first_nme
      ,EC.emrg_middle_nme
      ,EC.emrg_home_phn
      ,EC.emrg_wrk_phn
      ,EC.emrg_mobl_phn
      ,'SP' as most_recnt_trm_enr
      ,'0708' as most_recnt_yr_enr
      ,am.phone  
 
    FROM (select * 
            from name_master
           where nm.first_name is not null     
         ) as nm 
   Inner join (select id_num,max(phone) as phone 
                 from address_master
               ) as am 
      on nm.id_num = am.id_num 
    LEFT OUTER JOIN 
              (select a.*
                 from cm_emerg_contacts as a
                Where not Exists
                      (select id_num
                         from cm_emerg_contacts as x
                         Where a.id_num=x.id_num
                           and (
                               coalesce(a.emrg_last_name+' ','')+
                               coalesce(a.emrg_first_name+' ','')+
                               coalesce(a.emrg_middle_name+' ','')
                             <      
                               coalesce(x.emrg_last_name+' ','')+
                               coalesce(x.emrg_first_name+' ','')+
                               coalesce(x.emrg_middle_name+' ','')
                               )
                            or (  
                               coalesce(a.emrg_last_name+' ','')+
                               coalesce(a.emrg_first_name+' ','')+
                               coalesce(a.emrg_middle_name+' ','')
                             =      
                               coalesce(x.emrg_last_name+' ','')+
                               coalesce(x.emrg_first_name+' ','')+
                               coalesce(x.emrg_middle_name+' ','')
                             and coalesce(a.emrg_mobl_phn,a.emrg_wrk_phn,a.emrg_home_phn)
                              < coalesce(x.emrg_mobl_phn,x.emrg_wrk_phn,x.emrg_home_phn)
                               )
                       )   
              ) as EC
      ON nm.id_num = EC.id_num
 
   Where Exists
          (select id_num 
            from student_master as sm
           Where most_recnt_yr_enr = '0708'
             AND most_recnt_trm_enr = 'SP'
             and sm.id_num=nm.id_num 
          ) 
 
   ORDER BY nm.last_name ASC
           ,EC.emrg_last_nme ASC 

Open in new window

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
LowfatspreadCommented:
however this form of the select may be more appropriate for your needs
especially since the emergency contact information is optional...

hth
SELECT 
       nm.id_num
      ,nm.last_name
      ,nm.mobile_phone
      ,nm.first_name
      ,nm.middle_name
      ,coalesce( 
              (select top 1 coalesce(a.emrg_last_name+' ','')+
                      coalesce(a.emrg_first_name+' ','')+
                      coalesce(a.emrg_middle_name+' ','')+
                      ' '+
                      coalesce(a.emrg_mobl_phn+' ','NO MOBILE phn')+
                      coalesce(a.emrg_wrk_phn+' ','NO WORK phn')+
                      coalesce(a.emrg_home_phn,'No Home phn')
 
                 from cm_emerg_contacts as a
                Where not Exists
                      (select id_num
                         from cm_emerg_contacts as x
                         Where a.id_num=x.id_num
                           and (
                             <      
                               coalesce(x.emrg_last_name+' ','')+
                               coalesce(x.emrg_first_name+' ','')+
                               coalesce(x.emrg_middle_name+' ','')
                               )
                            or (  
                               coalesce(a.emrg_last_name+' ','')+
                               coalesce(a.emrg_first_name+' ','')+
                               coalesce(a.emrg_middle_name+' ','')
                             =      
                               coalesce(x.emrg_last_name+' ','')+
                               coalesce(x.emrg_first_name+' ','')+
                               coalesce(x.emrg_middle_name+' ','')
                             and coalesce(a.emrg_mobl_phn,a.emrg_wrk_phn,a.emrg_home_phn)
                              < coalesce(x.emrg_mobl_phn,x.emrg_wrk_phn,x.emrg_home_phn)
                               )
                       )   
                 and a.id_num = nm.id_num
 
              ),'No Contact Details Available') as Emerg_Contact_details
 
      ,'SP' as most_recnt_trm_enr
      ,'0708' as most_recnt_yr_enr
      ,am.phone  
 
    FROM (select * 
            from name_master
           where nm.first_name is not null     
         ) as nm 
   Inner join (select id_num,max(phone) as phone 
                 from address_master
               ) as am 
      on nm.id_num = am.id_num 
    
   Where Exists
          (select id_num 
            from student_master as sm
           Where most_recnt_yr_enr = '0708'
             AND most_recnt_trm_enr = 'SP'
             and sm.id_num=nm.id_num 
          ) 
 
   ORDER BY nm.last_name ASC

Open in new window

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
Query Syntax

From novice to tech pro — start learning today.