MaybeSQL.sql

SELECT a1.*
FROM attribute a1
WHERE a1.Name = 'referencenumber' AND a1.value LIKE '%A123%';

SELECT a0.*
FROM attribute a0
WHERE a0.userid IN (
  SELECT a1.userid
  FROM attribute a1
  WHERE a1.Name = 'referencenumber' AND a1.value LIKE '%A123%');

SELECT a0.*
FROM attribute a0
WHERE a0.userid IN (
  SELECT a1.userid
  FROM attribute a1
  WHERE a1.Name = 'referencenumber' AND a1.value LIKE '%A123%'
        AND exists(SELECT 1
                   FROM attribute a2
                   WHERE a2.userid = a1.userid AND a2.Name = 'referencenumberprefix' AND a2.value LIKE '%Justiz%'));

SELECT a0.*
FROM attribute a0
WHERE a0.userid IN (
  SELECT a1.userid
  FROM attribute a1
  WHERE a1.Name = 'referencenumber' AND a1.value LIKE '%A123%'
        AND EXISTS(SELECT 1
                   FROM attribute a2
                   WHERE a2.userid = a1.userid AND a2.Name = 'referencenumberprefix' AND a2.value LIKE '%Justiz%'
                         AND EXISTS(SELECT 1
                                    FROM attribute a3
                                    WHERE a3.userid = a1.userid AND a3.Name = 'accountgroup' AND
                                          a3.value LIKE '%group01%')));

-- an ldap query to the sql query above:
-- (&(l=*Berlin*)(sn=*Muster*)(givenName=*Hans*))

SELECT a0.*
FROM attribute a0
  JOIN attribute s0 ON a0.userid = s0.userid
WHERE s0.Name = 'demailaddress' AND a0.userid IN (
  SELECT a1.userid
  FROM attribute a1
    JOIN attribute s1 ON a1.userid = s1.userid
  WHERE s1.Name = 'demailaddress' AND
        a1.Name = 'referencenumber' AND a1.value LIKE '%A123%'
        AND EXISTS(SELECT 1
                   FROM attribute a2
                   WHERE a2.userid = a1.userid AND
                         a2.Name = 'referencenumberprefix' AND a2.value LIKE '%Justiz%'
                         AND EXISTS(SELECT 1
                                    FROM attribute a3
                                    WHERE a3.userid = a1.userid AND
                                          a3.Name = 'accountgroup' AND a3.value LIKE '%group01%'))
  ORDER BY s1.value DESC
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY)
ORDER BY s0.value DESC;

SELECT
  UserId,
  Name,
  Value
FROM Attribute
ORDER BY UserId
  OFFSET 0 ROWS
  FETCH NEXT 10 ROWS ONLY;