CODEWITHSUNDEEP
sql-servert-sqlif-statementselectjoin
Ryden Choi
Ryden Choi

Reputation: 307

SQL: How to select another table's row in if - select statement?

I`m making a stored procedure like this for Company's tree

There's 2 tables. tb_Dept and tb_Company. Table`s data is like this.

tb_dept 
CompanyCode    DeptCode    DisplayName    ParentDeptCode    DeptEmail                DisplayYN    SortOrder  
H101           1A07        RnD            1000              [email protected]             Y             0
H101           1907        Solution       1000              [email protected]        Y             0
H101           1A34        Management     1000              [email protected]      Y             0
H101           1000        CEO            org               [email protected]             Y             0

tb_Company
CompanyCode    CompanyName    GIDCode
H101           TestIT         1000
K101           DevelopeIT     1001 
S101           AnalysisIT     1024

At first if-select statement, result is below.

DeptCode    DeptName    HasSubDept    DeptEmail    CompanyCode    CompanyName 
H101        TestIT      0                          H101           TestIT

And I want to add this result 

DeptCode    DeptName    HasSubDept    DeptEmail    CompanyCode    CompanyName 
H101        CEO         0                          H101           TestIT

So, Finally first If- select statement result is like this.

DeptCode    DeptName    HasSubDept    DeptEmail    CompanyCode    CompanyName 
H101        TestIT      0                          H101           TestIT     
H101        CEO         0                          H101           TestIT

Yeah, I know it looks really strange, but I must make like that.

How can I modify first if statement or add another code?

Please somebody help me. My Stored procedure is below.

USE TestChart
GO
DECLARE @companyCode    varchar(20)
    ,   @parentDeptCode varchar(20)
    ,   @isRelative     bit
    ,   @langCode       varchar(20)

SELECT @companyCode     = 'H101'
       ,@parentDeptCode =  ''
       ,@isRelative     = 0
       ,@langCode       = 'ko'


IF @parentDeptCode IS NULL OR @parentDeptCode = '' 
    BEGIN
        SELECT
                CompanyCode AS DeptCode
            ,   CompanyName AS DeptName
            ,   dbo.uf_HasChildDept(CompanyCode) AS HasSubDept
            ,   '' AS DeptEmail
            ,   CompanyCode
            ,   CompanyName
        FROM dbo.tb_Company
        WHERE CompanyCode = @companyCode
    END 
ELSE
    BEGIN
        SELECT
            D.DeptCode
        ,   ISNULL(D.DisplayName, '') AS DeptName
        ,   dbo.uf_HasChildDept(D.DeptCode) AS HasSubDept
        ,   D.DeptEmail
        ,   C.CompanyCode
        ,   C.CompanyName
        FROM dbo.tb_Dept D WITH (NOLOCK)
        INNER JOIN dbo.tb_Company C WITH (NOLOCK)
        ON C.CompanyCode = @companyCode
        WHERE D.ParentDeptCode = @parentDeptCode
        AND D.DisplayYN = 'Y'
        AND c.CompanyCode = d.CompanyCode
        ORDER BY D.SortOrder
    END

Upvotes: 1

Views: 73

Answers (1)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

You should use UNION ALL.

You can include your @parentDeptCode IS NULL OR @parentDeptCode = '' condition in WHERE clause if you want.

SELECT
        CompanyCode AS DeptCode
    ,   CompanyName AS DeptName
    ,   dbo.uf_HasChildDept(CompanyCode) AS HasSubDept
    ,   '' AS DeptEmail
    ,   CompanyCode
    ,   CompanyName
FROM dbo.tb_Company
WHERE CompanyCode = @companyCode

UNION ALL

SELECT
        D.DeptCode
    ,   ISNULL(D.DisplayName, '') AS DeptName
    ,   dbo.uf_HasChildDept(D.DeptCode) AS HasSubDept
    ,   D.DeptEmail
    ,   C.CompanyCode
    ,   C.CompanyName
FROM dbo.tb_Dept D WITH (NOLOCK)
INNER JOIN dbo.tb_Company C WITH (NOLOCK) ON C.CompanyCode = D.CompanyCode
WHERE (ISNULL(@parentDeptCode, '') = '' AND D.ParentDeptCode = 'org') OR D.ParentDeptCode = @parentDeptCode)
    AND D.DisplayYN = 'Y'
    AND C.CompanyCode = @companyCode
ORDER BY D.SortOrder

Upvotes: 1

Related Questions