December 7, 2011 at 5:51 am
Below is my query ,
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName] from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=42 and b.degreeid=83
and b.streamgroupid LIKE '%,522,%'
and a.citygroupid=6
group by a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId],[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName],[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName]ORDER BY a.CollegeName
union all
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName] from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=42 and b.degreeid=83
and b.streamgroupid LIKE '522,%'
--or b.streamgroupid LIKE '%522,'
--or b.streamgroupid LIKE '522%'
and a.citygroupid=6
group by a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId],[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName],[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName]ORDER BY a.CollegeName
union all
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName] from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=42 and b.degreeid=83
and b.streamgroupid LIKE '%522,'
--or b.streamgroupid LIKE '522%'
and a.citygroupid=6
group by a.[CollegeId],[CollegeLogo],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId],[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName],[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin],a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName],a.[CountryId],[CountryName]ORDER BY a.CollegeName
showing error like :-
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'union'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'union'.
Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'union'.
how to use union syntax???
Thanks & Regards,
Pallavi
December 7, 2011 at 6:01 am
union or union all can only have 1 order by so you will need to remove the first two order by
eg
select
col1, col2, col3
from
table1
union [all]
select
col1, col2, col3
from
table2
union [all]
select
col1, col2, col3
from
table3
order by col1, col2, col3
December 7, 2011 at 6:02 am
You can order by only AFTER the unions <all>.
& reformatted for my sanity.
SELECT
ROW_NUMBER() OVER ( ORDER BY a.CollegeName ) + 1 as SrNo
, a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName]
from
collegemaster a
inner join college_degreestreamtransition b
on a.collegeid = b.collegeid
inner join College_UniversityMaster c
on a.universityid = c.universityid
inner join College_AccredationMaster d
on a.AccredationId = d.AccredationId
inner join College_ApprovedMaster e
on a.approvedid = e.approvedid
inner join College_TrustMaster f
on a.trustid = f.trustid
inner join College_CityMaster g
on a.cityid = g.cityid
inner join College_Citygroupmaster h
on a.citygroupid = h.citygroupid
inner join College_StateMaster i
on a.stateid = i.stateid
inner join college_countrymaster j
on a.countryid = j.countryid
where
b.coursetypeid = 42
and b.degreeid = 83
and b.streamgroupid LIKE '%,522,%'
and a.citygroupid = 6
group by
a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName] --ORDER BY a.CollegeName
union all
SELECT
ROW_NUMBER() OVER ( ORDER BY a.CollegeName ) + 1 as SrNo
, a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName]
from
collegemaster a
inner join college_degreestreamtransition b
on a.collegeid = b.collegeid
inner join College_UniversityMaster c
on a.universityid = c.universityid
inner join College_AccredationMaster d
on a.AccredationId = d.AccredationId
inner join College_ApprovedMaster e
on a.approvedid = e.approvedid
inner join College_TrustMaster f
on a.trustid = f.trustid
inner join College_CityMaster g
on a.cityid = g.cityid
inner join College_Citygroupmaster h
on a.citygroupid = h.citygroupid
inner join College_StateMaster i
on a.stateid = i.stateid
inner join college_countrymaster j
on a.countryid = j.countryid
where
b.coursetypeid = 42
and b.degreeid = 83
and b.streamgroupid LIKE '522,%'
--or b.streamgroupid LIKE '%522,'
--or b.streamgroupid LIKE '522%'
and a.citygroupid = 6
group by
a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName] --ORDER BY a.CollegeName
union all
SELECT
ROW_NUMBER() OVER ( ORDER BY a.CollegeName ) + 1 as SrNo
, a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName]
from
collegemaster a
inner join college_degreestreamtransition b
on a.collegeid = b.collegeid
inner join College_UniversityMaster c
on a.universityid = c.universityid
inner join College_AccredationMaster d
on a.AccredationId = d.AccredationId
inner join College_ApprovedMaster e
on a.approvedid = e.approvedid
inner join College_TrustMaster f
on a.trustid = f.trustid
inner join College_CityMaster g
on a.cityid = g.cityid
inner join College_Citygroupmaster h
on a.citygroupid = h.citygroupid
inner join College_StateMaster i
on a.stateid = i.stateid
inner join college_countrymaster j
on a.countryid = j.countryid
where
b.coursetypeid = 42
and b.degreeid = 83
and b.streamgroupid LIKE '%522,'
--or b.streamgroupid LIKE '522%'
and a.citygroupid = 6
group by
a.[CollegeId]
, [CollegeLogo]
, a.[CollegeName]
, a.PaidClientPage
, b.[CourseTypeId]
, b.[DegreeId]
, [CollegeDescription]
, [TypeofCollege]
, [Grade]
, a.[TrustId]
, [TrustName]
, a.[UniversityId]
, [UniversityName]
, a.[ApprovedId]
, [ApprovedName]
, a.[AccredationId]
, [AccredationName]
, [Status]
, [YearofEst]
, [CollegeAccType]
, [AddressStreet]
, [AddressPin]
, a.[CityId]
, [CityName]
, a.[CityGroupId]
, [CityGroupName]
, a.[StateId]
, [StateName]
, a.[CountryId]
, [CountryName]
--You can order by only AFTER the unions <all>
ORDER BY
CollegeName
December 7, 2011 at 6:04 am
Any reason you're doing 3 selects here? I've only skimmed the queries but they look extremely similar!
December 7, 2011 at 6:11 am
Actually i am running this script in store procedure,
ALTER procedure [dbo].[proc_GetCollegeData24]
(
@CourseId numeric(18,0),
@DegreeId numeric(18,0),
@streamid varchar(MAX)=null,
@CityGroupId numeric(18,0)=null,
@mode char
)
As
-- to get college data when stream id is given and citygroup id is specified
------------ Four counditions for paid-------
if (@mode='A')
begin
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo,a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData1
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '%'+','+@streamid+','+'%'
--or b.streamgroupid LIKE @streamid+','+'%'
--or b.streamgroupid LIKE '%'+','+'%'+@streamid
--or b.streamgroupid LIKE @streamid+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'Paid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData2
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE @streamid+','+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'Paid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData3
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '%'+','+'%'+@streamid
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'Paid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName] INTO #tempCollegeData4
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE @streamid+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'Paid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
------------ Four counditions for Unpaid-------
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData5
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '%'+','+@streamid+','+'%'
--or b.streamgroupid LIKE @streamid+','+'%'
--or b.streamgroupid LIKE '%'+','+'%'+@streamid
--or b.streamgroupid LIKE @streamid+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'unpaid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData6
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE @streamid+','+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'unpaid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData7
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '%'+','+'%'+@streamid
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'unpaid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData8
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE @streamid+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'unpaid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
SELECT * FROM #tempCollegeData1 union all SELECT * FROM #tempCollegeData2
union all SELECT * FROM #tempCollegeData3 union all SELECT * FROM #tempCollegeData4
union all SELECT * FROM #tempCollegeData5 union all SELECT * FROM #tempCollegeData6
union all SELECT * FROM #tempCollegeData7 union all SELECT * FROM #tempCollegeData8
DROP TABLE #tempCollegeData1
DROP TABLE #tempCollegeData2
DROP TABLE #tempCollegeData3
DROP TABLE #tempCollegeData4
DROP TABLE #tempCollegeData5
DROP TABLE #tempCollegeData6
DROP TABLE #tempCollegeData7
DROP TABLE #tempCollegeData8
end
but if i use union all it gives duplicate id record to me
and if i use intersect then it doesn't show any record...
why??
Thanks & Regards,
Pallavi
December 7, 2011 at 6:20 am
UNION ALL does give duplicates, if you dont want duplicates use UNION
December 7, 2011 at 6:22 am
anthony.green (12/7/2011)
UNION ALL does give duplicates, if you dont want duplicates use UNION
And be prepared for a hell of a performance hit with any significant amount of data.
December 7, 2011 at 6:29 am
yah...it's running 🙂 thanks a lot...:hehe:
Thanks & Regards,
Pallavi
December 7, 2011 at 6:36 am
can you please tell me could i reduce the query in store procedure???
because if run run below, then it does not fetch any data,
SELECT ROW_NUMBER() OVER(ORDER BY a.CollegeName) + 1 as SrNo, a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]INTO #tempCollegeData5
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '%'+','+@streamid+','+'%'
or b.streamgroupid LIKE @streamid+','+'%'
or b.streamgroupid LIKE '%'+','+'%'+@streamid
or b.streamgroupid LIKE @streamid+'%'
and a.citygroupid=@CityGroupId and a.CollegeAccType = 'unpaid'
group by a.[CollegeId],a.[CollegeName], a.PaidClientPage,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade],a.[TrustId],[TrustName],a.[UniversityId],[UniversityName],a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType],[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName],a.[StateId],[StateName]
,a.[CountryId],[CountryName]ORDER BY a.CollegeName
Thanks & Regards,
Pallavi
December 7, 2011 at 6:40 am
What do you mean by reduce??
Do the query return anything if you take out all the filters? If so add then 1 by one untill you find the one that filters out all the results.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply