Link to home
Start Free TrialLog in
Avatar of jasonbrandt3
jasonbrandt3

asked on

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

Avatar of Chris Mangus
Chris Mangus
Flag of United States of America image

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

isn't the phone always different?


 
By the looks of it, the last column is different each time.

So NFF. No Fault Found.
Sorry for the dup posting LFS.
Avatar of jasonbrandt3
jasonbrandt3

ASKER

So the syntax is ok.  Maybe i can group on the name?
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

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



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...
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.
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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