Combining multiple queries into one

Hi,

I have 3 different queries and i want to get the count of each query in a single query.


Below are my queries. I want the result as

Count of Query A Count of Query B Count of Query c
-----------------------------------------------------
10 20 13

Can this be achieved using stored procedures. Any idea will be very much appreciated.

Query A:

SELECT tbl_customer.ClientCode
FROM tbl_customer INNER JOIN tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID
WHERE (tbl_customer.ClientCode NOT IN
(SELECT clientcode
FROM tbl_customerfup)) AND (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL)


Query B:

SELECT tbl_customer.ClientCode
FROM tbl_customer INNER JOIN
tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID INNER JOIN
tbl_mastersector ON tbl_customer.SectorCode = tbl_mastersector.SectorCode INNER JOIN
tbl_customerfup ON tbl_customer.ClientCode = tbl_customerfup.ClientCode
WHERE (tbl_customerfup.Fup =
(SELECT MAX(fup)
FROM tbl_customerfup
WHERE tbl_customer.ClientCode = tbl_customerfup.ClientCode) AND (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL))

Query C:

SELECT tbl_customer.ClientCode
FROM tbl_customer INNER JOIN
tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID INNER JOIN
tbl_mastersector ON tbl_customer.SectorCode = tbl_mastersector.SectorCode INNER JOIN
tbl_customerfup ON tbl_customer.ClientCode = tbl_customerfup.ClientCode
WHERE (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customerfup.Fup IN
(SELECT fup
FROM tbl_customerfup
WHERE tbl_customer.ClientCode = tbl_customerfup.ClientCode AND Sent = 'Proposal')) AND
(tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL)

Thanks and Regards
SS

after each select just use @@rowCount and store it into a var because you will loose it .
like this
select A.....
set CountOfA = @@rowcount

select b...
set CountOfB = @@rowcount
select C.....
set CountOfC = @@rowcount

at the end

select CountOfA, CountOfB, CountOfC

Hi sypher,

Your reply was of great use. I modified a bit and worked great. Thanks so much.

My stored procedure is as follows.

CREATE PROCEDURE dbo.SummarySQL @v varchar(10) AS


begin

Declare @rc1 int
Declare @rc2 int
Declare @rc3 int



SELECT @rc1=count(tbl_customer.ClientCode)
FROM tbl_customer INNER JOIN
tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID INNER JOIN
tbl_mastersector ON tbl_customer.SectorCode = tbl_mastersector.SectorCode INNER JOIN
tbl_customerfup ON tbl_customer.ClientCode = tbl_customerfup.ClientCode
WHERE (tbl_customerfup.Fup =
(SELECT MAX(fup)
FROM tbl_customerfup
WHERE tbl_customer.ClientCode = tbl_customerfup.ClientCode) AND (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL))





SELECT @rc2=count( tbl_customer.ClientCode)
FROM tbl_customer INNER JOIN
tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID INNER JOIN
tbl_mastersector ON tbl_customer.SectorCode = tbl_mastersector.SectorCode INNER JOIN
tbl_customerfup ON tbl_customer.ClientCode = tbl_customerfup.ClientCode
WHERE (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customerfup.Fup IN
(SELECT fup
FROM tbl_customerfup
WHERE tbl_customer.ClientCode = tbl_customerfup.ClientCode AND Sent = 'Proposal')) AND
(tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL)




SELECT @rc3=count(tbl_customer.ClientCode)
FROM tbl_customer INNER JOIN tbl_loginaccount ON tbl_customer.EmployeeID = tbl_loginaccount.EmployeeID
WHERE (tbl_customer.ClientCode NOT IN
(SELECT clientcode
FROM tbl_customerfup)) AND (tbl_customer.FirstCall <> '' OR
tbl_customer.FirstCall IS NOT NULL) AND (tbl_customer.DiscussionSummary <> '' OR
tbl_customer.DiscussionSummary IS NOT NULL)



select @rc1 as a,@rc2 as b , @rc3 as c

end
GO


Thanks and regards
SS

glad to serve!!

So i have 3 queries

Query 1 Student - shows student name and attendance based on empid
From courseReg table.

SQL = SELECT tblAuthor.name, courseReg.attended, courseReg.CourseID
FROM tblAuthor INNER JOIN courseReg ON tblAuthor.employee_code = courseReg.empid
WHERE (((courseReg.empid)="1175"))
or = (((courseReg.empid)='" & empid & "'))


Query 2 Courses - shows all course Titles
From [Course Titles] table

SQL = SELECT [Course Titles].Title, Courses.ID AS albert
FROM [Course Titles] LEFT JOIN Courses ON [Course Titles].ID = Courses.TitleID

query 3 Report

combines the two queries above in one that shows student name
attendance all all Course Titles

SQL = SELECT student.name, course.Title, student.attended
FROM student RIGHT JOIN course ON student.CourseID = course.albert

The problem is back at the first query (student). The report
query WELL AS LONG AS as i give it a value like 1175 in the student
query.

I have a dynamic form that shows all student names and then passes
the selected empid value to another asp page were that it has query 3 (report).

I don't know how to make query 3 run with the empid coming from a form and not being set at the datebase.

I hope i made my self clear.
So thank for your help or if here is another silution.

You can try something like the following. Replace the student id with the parameter. In the asp page instead of SQL query you execute the stored procedure

Stored Procedure:

CREATE PROCEDURE dbo.QrySQL @v varchar(60) AS

Begin

SELECT tblAuthor.name, courseReg.attended, courseReg.CourseID
FROM tblAuthor INNER JOIN courseReg ON tblAuthor.employee_code = courseReg.empid
WHERE (((courseReg.empid)=@v))
or = (((courseReg.empid)='" & empid & "'))


.......

end

Go

Executing the Stored Procedure:

sql = "EXECUTE QrySql '1175'"


Hope this helps you.
SS