Sql Query optimization Issue in JOIN

Question

I have multiple JOIN statements in one query and I have to check two settings in the JOIN. How to do that in best way?

table: WebSettings
-------------------------------------------------------
Name            | Setting   | InstID    | WebSettingID
-------------------------------------------------------
'EnableError'   | 2 | 1111  | 1
'ErrorsSetting' | 0 | 2121  | 2

Index :

InstID
WebSettingID (InstID)


table: InstProd
-------------------------------------------------------
InstitutionID   | Name
-------------------------------------------------------
1111        | 'Bank of Ind'
2121        | 'IOB'


Index :

InstitutionID


SELECT 

Column1,
DATEADD(.....)

FROM 
dbo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON
WS1.InstitutionID = I.InstID AND
WS1.Name = 'EnableError' AND WS1.Setting=2
INNER JOIN dbo.WebSettings WS2 ON
WS2.InstitutionID = I.InstID AND
WS2.Name = 'ErrorsSetting' AND WS2.Setting=0
WHERE.....

I want to check one WebSettings = 2 and other = 0 in the JOIN

Is there a best solution to do so?

Answer 1

You can decide to not use JOIN, as my following solutions

If you want all InstProd with EnableError = 2 and ErrorSettings = 0

Try this:

SELECT I.*
FROM InstProd I
WHERE EXISTS
    (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2)
AND EXISTS
   (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0)

If you want all InstProd with check about two setting values

Try this:

SELECT I.*,
CASE
    WHEN
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2) > 0
        AND
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0) > 0
  THEN 'OK'
  ELSE 'KO'
END
FROM InstProd I