Easy question about combining queries

20 Jul 2006 4:04 PM

Jibber

I am somewhat of a T-SQL beginner, so excuse the simple question. I need to
perform the following queries, but I am sure they could be combined into a
more efficient single query. How would I combine these into the most optimal
query? This segment is from my stored procedure.

DECLARE
@classCount int,
@completedExam int

SELECT @classCount = COUNT(*)
FROM Student..tblSchedule
WHERE user_id=@user_id

SELECT account_created, account_approved
FROM Student..tblProfile
WHERE user_id=@user_id

SELECT @completedExam = COUNT(*)
FROM Exam..tblCompletedExam
WHERE user_id=@user_id

Thanks!

20 Jul 2006 4:30 PM

Arnie Rowland

Use SubQueries, somewhat like this:

SELECT
     Account_Created
   , Account_Approved
   , ClassCount    = ( SELECT count(*) FROM Student..tblSchedule WHERE User_ID = @User_ID )
   , CompletedExam = ( SELECT count(*) FROM Exam..tblCompletedExam WHERE User_ID = @User_ID )
FROM Student..tblProfile
WHERE User_ID = @User_ID

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Jibber" <nospam@jibberjabberish.com> wrote in message news:OZRQpYBrGHA.1852@TK2MSFTNGP03.phx.gbl...
>I am somewhat of a T-SQL beginner, so excuse the simple question. I need to
> perform the following queries, but I am sure they could be combined into a
> more efficient single query. How would I combine these into the most optimal
> query? This segment is from my stored procedure.
>
> DECLARE
> @classCount int,
> @completedExam int
>
> SELECT @classCount = COUNT(*)
> FROM Student..tblSchedule
> WHERE user_id=@user_id
>
> SELECT account_created, account_approved
> FROM Student..tblProfile
> WHERE user_id=@user_id
>
> SELECT @completedExam = COUNT(*)
> FROM Exam..tblCompletedExam
> WHERE user_id=@user_id
>
> Thanks!
>
>

20 Jul 2006 7:05 PM

Jibber

Thanks! Worked great...

"Arnie Rowland" <ar***@1568.com> wrote in message news:eHFdgnBrGHA.4508@TK2MSFTNGP04.phx.gbl...

Use SubQueries, somewhat like this:

SELECT
       Account_Created
     , Account_Approved
     , ClassCount    = ( SELECT count(*) FROM Student..tblSchedule WHERE User_ID = @User_ID )
     , CompletedExam = ( SELECT count(*) FROM Exam..tblCompletedExam WHERE User_ID = @User_ID )
FROM Student..tblProfile
WHERE User_ID = @User_ID

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Jibber" <nospam@jibberjabberish.com> wrote in message news:OZRQpYBrGHA.1852@TK2MSFTNGP03.phx.gbl...
>I am somewhat of a T-SQL beginner, so excuse the simple question. I need to
> perform the following queries, but I am sure they could be combined into a
> more efficient single query. How would I combine these into the most optimal
> query? This segment is from my stored procedure.
>
> DECLARE
> @classCount int,
> @completedExam int
>
> SELECT @classCount = COUNT(*)
> FROM Student..tblSchedule
> WHERE user_id=@user_id
>
> SELECT account_created, account_approved
> FROM Student..tblProfile
> WHERE user_id=@user_id
>
> SELECT @completedExam = COUNT(*)
> FROM Exam..tblCompletedExam
> WHERE user_id=@user_id
>
> Thanks!
>
>