Combine two queries using union

Question

0.00/5 (No votes)

See more:

Can you help me to combine the following two queries using UNION
Query 1:

SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment1 as Installment1,
	sf.PaymentationDate1 as PaymentationDate1,
	sf.InstallmentAmt1 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate1>=@fromdate  and sf.PaymentationDate1<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id 
  Query2:
    SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment2 as Installment,
	sf.PaymentationDate2 as PaymentationDate,
	sf.InstallmentAmt2 as InstallmentAmt
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate2>=@fromdate  and sf.PaymentationDate2<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id

Posted 18-Oct-11 21:31pm

Nimisha Mary John

Add a Solution

4 solutions

Solution 2

Just put the UNION[^] between the two SELECT[^] queries.

SQL

SELECT *
UNION
SELECT *

Posted 18-Oct-11 21:36pm

André Kraak

Solution 1

Open a text editor add the first query to it, then write UNION then add the second query.

SQL

query1
UNION
query2

Posted 18-Oct-11 21:35pm

Mehdi Gholam

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kishorekke · Accepted Answer · 2011-10-18T23:20:00

Before u do union/union all, both queries have same no of columns and also column datatypes also same.

-- UNION will retain the duplicates
-- UNION ALL will allow all the entries (allow duplicates)
-- Order by clause should use last

SELECT
sr.Reg_Id as [Reg Id],
se.Enquiry_Id as [Enquiry Id],
se.studentName as [StudentName],
se.ResPhone as [ResPhone],
se.MobileNo as [MobileNo],
tblUserDetails.UserName as Counselor,
[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
sf.Installment1 as Installment1,
sf.PaymentationDate1 as PaymentationDate1,
sf.InstallmentAmt1 as InstallmentAmt1
FROM
tblStudentsRegister sr INNER JOIN tblStudentsEnquiry se ON
sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id
and sf.PaymentationDate1>=@fromdate and sf.PaymentationDate1<= @todate
inner join tblUserDetails on
se.Councelor_Id = tblUserDetails.User_Id AND se.studentName like @studentname
AND se.Company_Id=5 and se.feestatus=0

UNION

SELECT
sr.Reg_Id as [Reg Id],
se.Enquiry_Id as [Enquiry Id],
se.studentName as [StudentName],
se.ResPhone as [ResPhone],
se.MobileNo as [MobileNo],
tblUserDetails.UserName as Counselor,
[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
sf.Installment2 as Installment,
sf.PaymentationDate2 as PaymentationDate,
sf.InstallmentAmt2 as InstallmentAmt
FROM
tblStudentsRegister sr INNER JOIN tblStudentsEnquiry se ON
sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id
and sf.PaymentationDate2>=@fromdate and sf.PaymentationDate2<= @todate
inner join tblUserDetails on
se.Councelor_Id = tblUserDetails.User_Id AND se.studentName like @studentname
AND se.Company_Id=5 and se.feestatus=0
ORDER BY 1 .

sachin10d · Accepted Answer · 2011-10-18T22:50:00

Try this

SQL

SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment1 as Installment1,
	sf.PaymentationDate1 as PaymentationDate1,
	sf.InstallmentAmt1 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate1>=@fromdate  and sf.PaymentationDate1<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 
  union
    SELECT  
	sr.Reg_Id as [Reg Id],
	se.Enquiry_Id as [Enquiry Id],
	se.studentName as [StudentName],
	se.ResPhone as [ResPhone],
	se.MobileNo as [MobileNo],
	tblUserDetails.UserName as Counselor,
	[dbo].fnGetStringOfCourseFromRegId(sr.Reg_Id) AS Courses,
	sf.Installment2 as Installment1,
	sf.PaymentationDate2 as PaymentationDate1,
	sf.InstallmentAmt2 as InstallmentAmt1
 	FROM
         	tblStudentsRegister sr  INNER JOIN tblStudentsEnquiry se ON
         	sr.Enquiry_Id=se.Enquiry_Id inner join tblFeeDatailsCalc sf on sf.EnqueryId=sr.Enquiry_Id 
         	and sf.PaymentationDate2>=@fromdate  and sf.PaymentationDate2<= @todate
         	 inner join tblUserDetails on 
         	se.Councelor_Id = tblUserDetails.User_Id  AND se.studentName like @studentname
         	AND se.Company_Id=5 and se.feestatus=0 ORDER BY  sr.Reg_Id

Combine two queries using union

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Combine two queries using union

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us