Check in T-SQL whether certain value sets exist in a related table-CodePudding

I have following tables:

User - userId, userName, ...

Settings - settingId, userId, settingKey, settingValue

for example for userId = 123, I might have:

settingId: 1
userId: 123
settingKey: "allowClient"
settingValue: "0"

settingId: 2
userId: 123
settingKey: "allowAccess"
settingValue: "1"

Then for example how can I query for all users that have settingValue of "0" corresponding to settingKey of "allowClient" and settingValue of "1" corresponding to settingKey of "allowAccess"? Sometimes the settingKey and settingValue that I'm looking for might not even be there for a particular user, in which case, I would just want to ignore those users.

My "attempt":

select * from User u inner join Settings s on u.userid = s.userid
where s.settingKey = 'allowClient and s.settingValue = '0'
and s.settingKey = 'allowAccess' and s.settingValue = '1'

this doesn't work for obvious reason because it's putting AND on all the conditions. I'm not aware of any sql construct that can get around this and allow me to just say what I actually want.

CodePudding user response：

Your first attempt doesn't work because the WHERE clause check each row one at a time. And no single row fulfils all of those conditions at once.

So, you could use an EXISTS() check on each of the two keys, for a very literal expression of your problem...

SELECT
  user.*
FROM
  user
WHERE
  EXISTS (
    SELECT *
      FROM settings
     WHERE userId = user.userId
       AND settingKey = 'allowClient'
       AND settingValue = '0'
  )
  AND
  EXISTS (
    SELECT *
      FROM settings
     WHERE userId = user.userId
       AND settingKey = 'allowAccess'
       AND settingValue = '1'
  )

Depending on data characteristics, you may benefit from a single sub-query instead of two EXISTS() checks.

This is closer to what you were trying to do.

Filter to get two rows per user (using OR instead of AND)
Aggregate back down to a single row and check if both conditions were met

(But I'd go with two EXISTS() first, and let the optimiser do its work.)

WITH
  matching_user
(
  SELECT
    userId
  FROM
    settings
  WHERE
    (settingKey = 'allowClient' AND settingValue = '0')
    OR
    (settingKey = 'allowAccess' AND settingValue = '1')
  GROUP BY
    userId
  HAVING
    COUNT(DISTINCT settingKey) = 2  -- DISTINCT only needed if one user has the same key set twice
)
SELECT
  user.*
FROM
  user
INNER JOIN
  matching_user
    ON user.userId = matching_user.userId

Finally, you could just join twice, which is functionally similar to the double-exists check, but shorter code, though not always as performant.

SELECT
  user.*
FROM
  user
INNER JOIN
  settings   AS s0
     ON  s0.userId       = user.userId
     AND s0.settingKey   = 'allowClient'
     AND s0.settingValue = '0'
INNER JOIN
  settings   AS s1
     ON  s1.userId       = user.userId
     AND s1.settingKey   = 'allowAccess'
     AND s1.settingValue = '1'

Using the two different aliases prevents ambiguity (which would cause an error).

It does assume that the joins will only ever find 0 or 1 rows, if they can find many, you get duplication. EXISTS() doesn't have that problem.