将2个TSQL语句合并为1

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

我需要将以下两个SQL语句合并为一个。任何帮助是极大的赞赏。

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.

但是，即使语句2没有结果，我也需要从语句1返回结果。

6 个解决方案

#1

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:

在您的客户端代码中，您可以这样调用它：

Create a command object for the GetAllData stored procedure.
为GetAllData存储过程创建一个命令对象。
Attach a parameter object for the @NumberOfRecipients parameter.
附加@NumberOfRecipients参数的参数对象。
Execute the command.
执行命令。
Consume the default resultset.
使用默认结果集。
Read the @NumberOfRecipients parameter value (this must be done after consuming the resultset).
读取@NumberOfRecipients参数值（这必须在使用结果集后完成）。

C# example:

C＃示例：

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.:

您还可以在服务器端T-SQL代码中重用存储过程，例如：

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

#2

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

#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

#4

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

#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

#6

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

#1