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.

Answer

You can insert your first query data in table variable and then fetch the id from table variable in second query.Something like below.

Code:

 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 )