Executing Sql \ Function Subqueries

Question

I am currently working on a particularly complex use case. Simplification below :)

First, a customer record has a many-to-one relationship with a set of services, that is, one client can have several services associated with it.

In my trigger, I write a request that returns a client identifier according to certain criteria. The criteria are as follows:

• If at least one service is of type B and there are no services of type A, return id
• If at least one service is of type C and there are no services of type B or A, return id
• If at least one service is of type D and there are no services of type C or B or A, return id

and my current approach is to generate a query similar to the query below

SELECT c.ClientId
FROM
  Clients AS c
    -- actually INNER JOIN is superfluous in this sample, but required for
    -- other auxilliary criteria i have left out. illustrates relationship
    -- between Clients and Services table
    INNER JOIN Services AS s ON c.ClientId = s.ClientId
WHERE
-- has at least one service of type B, no A
(EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'B')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'A'))) OR 

-- has at least one service of type C, no B, no A
(EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'C')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'B')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'A'))) OR

-- has at least one service of type D, no C, no B, no A
(EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'D')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'C')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'B')) AND
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'A')))

[dbo].[Get_ServicesByClientIdAndType] - , .

-- this query is actually significantly more complex than shown
-- below, but this illustrates use of parameters client id and
-- service type
SELECT s.ServiceType
FROM
  Services AS s
WHERE
  s.ClientId = @clientId AND
  s.ServiceType = @serviceType

---

, , [dbo].[Get_ServicesByClientIdAndType], ? [ , 9 !!! Sql Server 2005]

, Sql Server 2005 , , , [ ], Sql .

---

: , - . ​​ [ ]

SELECT c.ClientId
FROM
  Clients AS c
    INNER JOIN Services AS s ON c.ClientId = s.ClientId
WHERE
  NOT EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'A')) AND
    (EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'B')) OR 
    EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'C')) OR 
    EXISTS (SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'D')))

, B, , C D, . , . Arg! !

---

, - , Sql Server .

Answer 1

, , .

. , SELECT * FROM Get_ServicesByClientIdAndType (c.ClientId, 'A') . ;) .

, . , Get_ServicesByClientIdAndType. . , . , SQL Server.

, . ONCE.

, . Get_ServicesByClientIdAndType. ?

:

SELECT A.* FROM
(
 SELECT C.ClientID,
  SUM(CASE(S.ServiceType) WHEN 'A' THEN 1 ELSE 0 END) AS ServiceA,
  SUM(CASE(S.ServiceType) WHEN 'B' THEN 1 ELSE 0 END) AS ServiceB,
  SUM(CASE(S.ServiceType) WHEN 'C' THEN 1 ELSE 0 END) AS ServiceC,
  SUM(CASE(S.ServiceType) WHEN 'D' THEN 1 ELSE 0 END) AS ServiceD
 FROM Clients AS C
 INNER JOIN Services AS s ON c.ClientId = s.ClientId
 GROUP BY C.ClientID
) A
WHERE ((A.ServiceB > 0) AND (A.ServiceA = 0)) 
 OR ((A.ServiceC > 0) AND (A.ServiceA = 0) AND (A.ServiceB = 0))
 OR ((A.ServiceD > 0) AND (A.ServiceA = 0) AND (A.ServiceB = 0) AND (A.ServiceC = 0))

. , . . . .

:

ClientID ServiceA ServiceB ServiceC ServiceD
-------- -------- -------- -------- --------
26915       0        4        2        2
26917       0        0        1        1
26921       0        3        2        3
26927       0        4        2        4

, . , ;-) , . , . .

, , , succesfull join? .

, , , ; -)

, , - .

Answer 2

, sql- Get_ServicesByClientIdAndType , "". , 100 Client 300 .

, sql " ". , , .

Answer 3

, , - "", . "" , . , , NOT. FWIW, YMMV.: -)