时间戳

首页 > 解决方案 > How to use result of first query in second query where condition?

sql - How to use result of first query in second query where condition?

问题描述

I have to select two tables data, One is PackageData and another is PackageDataDetails. Here is PackageData is Parent table and PackageDataDetails is child table.

See below code:

SELECT 
    PD.Id, PD.MemberId, PD.Merchant, PD.Weight, PD.Remarks
FROM 
    [dbo].[PackageData] PD
INNER JOIN 
    Account A ON A.MemberId = PD.MemberId
INNER JOIN 
    Users U ON U.Id = A.UserId AND (@UserId IS NULL OR U.Id = @UserId)
WHERE 
    (@Status IS NULL OR @Status = '' OR  PD.SystemStatus = @Status)
    AND (@Id IS NULL OR PD.Id = @Id)
ORDER BY 
    MD.Id
    OFFSET @PageSize * (@PageNo+1 - 1) ROWS  
    FETCH NEXT @PageSize ROWS ONLY 
OPTION (RECOMPILE);

SELECT 
    PDD.Id, PDD.PackageDataId, PDD.Description, PDD.Quantity, PDD.Status
FROM 
    [dbo].[PackageDataDetails] PDD
INNER JOIN 
    [dbo].[PackageData] PD ON PD.Id = PDD.PackageDataId
WHERE 
    PDD.PackageDataId IN ({PD.Id of Above Select query})

This code block is written in a stored procedure. Here, I didn't want to rewrite the first query in where a condition of the second query.

I have to use this stored procedure in one API response to do things in one DB call only. Please share your idea to resolve this issue.

标签： sqlsql-server

解决方案

您可以在表变量中插入第一个查询数据，然后在第二个查询中从表变量中获取 id。如下所示。

代码：

 Declare @tblData TABLE
    ( 
        Id          Int,
        MemberId                    Int,
        Merchant    NVARCHAR(Max),
        Weight  NVARCHAR(Max),
        Remarks               Nvarchar(Max)

    );

   Insert Into @tblData
(
    Id,
    MemberId,
    Merchant,
    Weight,
    Remarks
)

SELECT PD.Id ,PD.MemberId, PD.Merchant,PD.Weight,PD.Remarks
FROM [dbo].[PackageData] PD
INNER JOIN Account A ON A.MemberId = PD.MemberId
INNER JOIN Users U ON U.Id = A.UserId AND (@UserId IS NULL OR U.Id = @UserId)
WHERE (@Status IS NULL OR @Status = '' OR  PD.SystemStatus = @Status)
AND (@Id IS NULL OR PD.Id = @Id)
ORDER BY MD.Id
OFFSET @PageSize * (@PageNo+1 - 1) ROWS  FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);

SELECT PDD.Id, PDD.PackageDataId, PDD.Description, PDD.Quantity, PDD.Status
FROM [dbo].[PackageDataDetails] PDD
INNER JOIN [dbo].[PackageData] PD ON PD.Id = PDD.PackageDataId
WHERE PDD.PackageDataId IN (select Id from @tblData )

推荐阅读