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
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
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?
isn't the phone always different?
By the looks of it, the last column is different each time.
So NFF. No Fault Found.
So NFF. No Fault Found.
Sorry for the dup posting LFS.
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...
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
>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...
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...
ASKER
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...
e.g.
Name
data data
data data
NextName
Hope this makes my question clearer...
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
however this form of the select may be more appropriate for your needs
especially since the emergency contact information is optional...
hth
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