i need distinct address_id in result no duplication. here query.
select distinct address.address_id, address.address1, address.streetcity, state.stateabbrev, rtrim(ltrim(case when address.streetzipcode not null , address.streetzipcode != 'null' address.streetzipcode else '' end))+case when len(address.streetzipplus4)>0 '-'+rtrim(ltrim(address.streetzipplus4)) else '' end streetzipcode, address.homephone, dbo.f_addressstudent (student.address_id) students, dbo.f_addresspeople (student.address_id) adults, case when @classif_id null 0 else student.classif_id end classif, classifctn district with(nolock) join dbo.building on building.district_id = district.district_id join dbo.studbldg_bridge with(nolock) on studbldg_bridge.bldg_id=building.bldg_id join dbo.student with(nolock) on student.student_id = studbldg_bridge.student_id join classif with(nolock) on student.classif_id = classif.classif_id left join dbo.address with(nolock) on student.address_id = address.address_id left join dbo.state with(nolock) on address.streetstate_id = state.state_id left join dbo.state mailstate with(nolock) on address.state_id = mailstate.state_id district.district_id = (select district_id dbo.building with(nolock) bldg_id = @bldg_id) order classif,adults, students here result of query query result error in data
i have tried group , use aggregate function address_id have non-aggregate columns didn't worked me. after tried using over(partition address.address_id) didn't worked.
any appreciated in advance.
thank
**update on business logic/requirements **
i need unique addresses parents of students. parent can have 2 or more children living in same address, causes duplication. need 1 child per parent in other words.
from image of results looks classifctn column has more 1 value repeating row that. in order 1 distinct address_id , rest of columns either remove query or can set precedence return 1 record per address_id
further please tag rdbms ware using. mysql example doesn't have window functions yet tagged yet referenced using over(partition.... not possible in mysql
;with cte ( select distinct address.address_id, address.address1, address.streetcity, state.stateabbrev, rtrim(ltrim(case when address.streetzipcode not null , address.streetzipcode != 'null' address.streetzipcode else '' end))+case when len(address.streetzipplus4)>0 '-'+rtrim(ltrim(address.streetzipplus4)) else '' end streetzipcode, address.homephone, dbo.f_addressstudent (student.address_id) students, dbo.f_addresspeople (student.address_id) adults, case when @classif_id null 0 else student.classif_id end classif, classifctn, row_number() on (partition address.address_id order how choose?) rownum district with(nolock) join dbo.building on building.district_id = district.district_id join dbo.studbldg_bridge with(nolock) on studbldg_bridge.bldg_id=building.bldg_id join dbo.student with(nolock) on student.student_id = studbldg_bridge.student_id join classif with(nolock) on student.classif_id = classif.classif_id left join dbo.address with(nolock) on student.address_id = address.address_id left join dbo.state with(nolock) on address.streetstate_id = state.state_id left join dbo.state mailstate with(nolock) on address.state_id = mailstate.state_id district.district_id = (select district_id dbo.building with(nolock) bldg_id = @bldg_id) ) select * cte rownum = 1 order classif ,adults ,students alternatively nest select query. note though solution useless return 1 grade/classifctn when more 1 exists in household if don't care column should remove query.
actually both classifctn , classif columns cause multiple rows when more 1 student @ same address. here way concatenate values single row. should spend more time on business case , defining us. here 1 example you:
select distinct address.address_id ,address.address1 ,address.streetcity ,state.stateabbrev ,ltrim(rtrim(isnull(nullif(address.streetzipcode,'null'),''))) + case when len(address.streetzipplus4) > 0 '-' else '' end + ltrim(rtrim(isnull(address.streetzipplus4,''))) streetzipcode ,address.homephone ,dbo.f_addressstudent (student.address_id) students ,dbo.f_addresspeople (student.address_id) adults , case when @classif_id null 0 else student.classif_id end classif ,stuff( (select ',' + cast(classif_id varchar(100)) classif c c.classif = student.classif xml path('')) ,1,1,'') classifs ,stuff( (select ',' + cast(classifctn varchar(100)) classif c c.classif = student.classif xml path('')) ,1,1,'') classifctns district with(nolock) inner join dbo.building on building.district_id = district.district_id , building.bldg_id = @bldg_id inner join dbo.student with(nolock) on student.student_id = studbldg_bridge.student_id inner join dbo.address with(nolock) on student.address_id = address.address_id left join dbo.state with(nolock) on address.streetstate_id = state.state_id note when ahead , changed zip code logic show use of isnull() , nullif() helpful in cases that. removed 3 tables because 2 not used , third ends being used in subselect concatenate values. address table changed inner join because if address doesn't exist of other information becomes blank/useless....
inner join dbo.studbldg_bridge with(nolock) on studbldg_bridge.bldg_id=building.bldg_id left join dbo.state mailstate with(nolock) on address.state_id = mailstate.state_id inner join classif with(nolock) on student.classif_id = classif.classif_id 
Comments
Post a Comment