Combining 2 TSQL statements into 1

Question

Hi,

I need to combine the following two SQL statements into one. Any help is greatly appreciated.

Thanks.

 SELECT C.*, M.members_Email
FROM tbl_Campaigns C
JOIN tbl_Members M
ON C.campaign_MemberId = M.members_Id 
WHERE C.campaign_MemberId = @userID
ORDER BY C.campaign_Key DESC


SELECT COUNT(*) FROM tbl_CampaignRecipients 
WHERE recip_CampaignId = C.campaign_Key AND 
(recipient_Status = 3 or recipient_Status = 4)

However, i need to return the results from statement 1 even if no results are present for statement 2.

Answer 1

You could use a subquery like:

SELECT    C.*
,         M.members_Email
,         (SELECT COUNT(*) 
           FROM tbl_CampaignRecipients 
           WHERE recip_CampaignId = C.campaign_Key
           AND recipient_Status = 3 or recipient_Status = 4) as RecipientCount
FROM      tbl_Campaigns C
JOIN      tbl_Members M
ON        C.campaign_MemberId = M.members_Id 
WHERE     C.campaign_MemberId = @userID
ORDER BY  C.campaign_Key DESC

Answer 2

SELECT C.*, M.members_Email, (SELECT ... insert 2nd SQL here ...)
  FROM tbl_campaigns C ... rest of 1st SQL here ...

Answer 3

Another possible method if the subquery runs slowly:

SELECT
     C.column_1,
     C.column_2,
     ...
     M.members_email,
     SQ.recipient_count
FROM
     Campaigns C
INNER JOIN Members M ON
     M.members_id = @user_id
LEFT OUTER JOIN
(
     SELECT
          CR.campaign_id,
          COUNT(*) AS recipient_count
     FROM
          Campaign_Recipients CR
     GROUP BY
          CR.campaign_id
) AS SQ ON
     SQ.campaign_id = C.campaign_id
WHERE
     C.campaign_member_id = @user_id

Answer 4

another way

SELECT C.*, M.members_Email,(SELECT COUNT(*) FROM tbl_CampaignRecipients 
WHERE recip_CampaignId = C.campaign_Key AND 
recipient_Status in( 3,4)) as TheCount
FROM tbl_Campaigns C
JOIN tbl_Members M
ON C.campaign_MemberId = M.members_Id 
WHERE C.campaign_MemberId =  @userID
ORDER BY C.campaign_Key DESC

example

create table test (id int, id2 int)
insert test values(1,1)
insert test values(1,2)
insert test values(1,3)
insert test values(2,1)
insert test values(2,1)

select *,(select count(*) from test where id = t1.id ) as the_count
 from test t1

Answer 5

one possible solution

SELECT C.*, M.members_Email, count(CR.recip_CampaignId) as CampaignCount
FROM tbl_Campaigns 
    JOIN tbl_Members ON C.campaign_MemberId = M.members_Id 
    LEFT JOIN tbl_CampaignRecipients CR on CR.recip_CampaignId = C.campaign_Key 
         AND (CR.recipient_Status = 3 or CR.recipient_Status = 4)
WHERE C.campaign_MemberId = @user
ORDER BY C.campaign_Key DESC

Answer 6

Why do you need to combine the two statements into one? However you manage to accomplish that, you will inflate the size of the resultset being passed over the network unnecessarily.

I suggest combining the two statements into a stored procedure instead:

CREATE PROCEDURE GetAllData (@NumberOfRecipients int output) AS BEGIN

SELECT C.*, M.members_Email
FROM tbl_Campaigns C
JOIN tbl_Members M ON C.campaign_MemberId = M.members_Id 
WHERE C.campaign_MemberId = @userID
ORDER BY C.campaign_Key DESC

SELECT @NumberOfRecipients = COUNT(*)
FROM tbl_CampaignRecipients 
WHERE recip_CampaignId = C.campaign_Key
AND (recipient_Status = 3 or recipient_Status = 4)

END

In your client-side code, you would call it like this:

1. Create a command object for the GetAllData stored procedure.
2. Attach a parameter object for the @NumberOfRecipients parameter.
3. Execute the command.
4. Consume the default resultset.
5. Read the @NumberOfRecipients parameter value (this must be done after consuming the resultset).

C# example:

using(SqlCommand command = new SqlCommand("GetAllData", connection)) {
    command.CommandType = CommandType.StoredProcedure;
    SqlParameter recipientsParam = new SqlParameter("@NumberOfRecipients", SqlDbType.Int) { Direction = ParameterDirection.Output };
    command.Parameters.Add(recipientsParam);
    using(SqlDataReader reader = command.ExecuteReader()) {
        // consume the resultset
    }
    // read the parameter
    int recipients = (int) recipientsParam.Value;
}

You can also re-use the stored procedure in server-side T-SQL code, e.g.:

declare @NumberOfRecipients int
insert into #Results execute GetAllData @NumberOfRecipients output