none
Using case statement in joins RRS feed

  • Question

  • Hi

    I have a query which gets me a dataset which is showing data perfectly fine. I want to get two different datasets for a column set to 0 or 1

     SELECT
                                             T1.Column1,
                                             T2.Column2,
                                             T3.Coulm3,
                                             T2.Column4,
                                             T2.Column5,
                                             T2.Column6
                    FROM                Table1 T1
                                            JOIN  Table2 T ON T2.COlumn3 = T1.COlumn1 /* I want to give a case statement here, I retrieve a role code if then role code = 'S' Then use T2.Column3 = T1.Column1 AND IsVisible = 1 ESLE T2.Column3 = T1.Column1*/
                                            JOIN Table3 T3 ON T3.Column5 = T2.Column1
                    WHERE          (T3.Column2 IN (SELECT * FROM dbo.checkIDs(@personID)))

    Tuesday, November 19, 2019 11:55 AM

Answers

  • You don't need a CASE Statement, simple boolean logic with AND/OR already meets your requirement =>

    SELECT T1.Column1, T2.Column2, T3.Coulm3, T2.Column4, T2.Column5, T2.Column6
    FROM Table1 T1
         JOIN  
         Table2 T 
            ON (RoleCode = 'S' AND T2.COlumn3 = T1.COlumn1 AND IsVisible = 1)
               OR 
               (RoleCode <> 'S' AND T2.Column3 = T1.Column1)
         JOIN 
         Table3 T3 ON T3.Column5 = T2.Column1
    WHERE (T3.Column2 IN (SELECT * FROM dbo.checkIDs(@personID))) 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by nikzk Tuesday, November 19, 2019 2:04 PM
    Tuesday, November 19, 2019 12:10 PM

All replies

  • Hi

    Your request is not clear, please provise some data sample, and how should your final result suppose to look like .


    Regards, David .

    Tuesday, November 19, 2019 12:05 PM
  • You don't need a CASE Statement, simple boolean logic with AND/OR already meets your requirement =>

    SELECT T1.Column1, T2.Column2, T3.Coulm3, T2.Column4, T2.Column5, T2.Column6
    FROM Table1 T1
         JOIN  
         Table2 T 
            ON (RoleCode = 'S' AND T2.COlumn3 = T1.COlumn1 AND IsVisible = 1)
               OR 
               (RoleCode <> 'S' AND T2.Column3 = T1.Column1)
         JOIN 
         Table3 T3 ON T3.Column5 = T2.Column1
    WHERE (T3.Column2 IN (SELECT * FROM dbo.checkIDs(@personID))) 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by nikzk Tuesday, November 19, 2019 2:04 PM
    Tuesday, November 19, 2019 12:10 PM
  • SELECT
                                             T1.Column1,
                                             T2.Column2,
                                             T3.Coulm3,
                                             T2.Column4,
                                             T2.Column5,
                                             T2.Column6
                    FROM                Table1 T1
             JOIN  Table2 T ON T2.COlumn3 =  case when rolecode = 'S'
       AND IsVisible = 1 then T1.Column1 esle T1.Column1 end

        JOIN Table3 T3 ON T3.Column5 = T2.Column1
                    WHERE          (T3.Column2 IN (SELECT * FROM dbo.checkIDs(@personID)))

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 19, 2019 12:12 PM
    Answerer
  • Thanks Olaf!

    Or condition pulls the 0 values too. Isvisible is a field from Table2 only. If that field is 1 then show just those rows else show everything. Its a stored procedure , I can easily achieve this by using IF ELSE and writing two selects but I want to manage it in JOINs using one query.

    Tuesday, November 19, 2019 12:31 PM
  • Thanks Olaf!

    I added Is Visible in second condition too and it worked fine.

    SELECT T1.Column1, T2.Column2, T3.Coulm3, T2.Column4, T2.Column5, T2.Column6
    FROM Table1 T1
         JOIN  
         Table2 T 
            ON (RoleCode = 'S' AND T2.COlumn3 = T1.COlumn1 AND IsVisible = 1)
               OR 
               (RoleCode <> 'S' AND T2.Column3 = T1.Column1 AND (
    IsVisible = 1 OR IsVisible = 0
    ))
         JOIN 
         Table3 T3 ON T3.Column5 = T2.Column1
    WHERE (T3.Column2 IN (SELECT * FROM dbo.checkIDs(@personID))) 


    • Edited by nikzk Tuesday, November 19, 2019 2:05 PM
    Tuesday, November 19, 2019 2:05 PM