Simplify MS SQL statements

Question

I read a great post on JOINS, What is the difference between "INNER JOIN" and "OUTER JOIN"? but I don’t think this helps my problem.

I would like to combine two select statements into one select AND statement, to make it efficient for any database queries.

That's what i

SELECT
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-11',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%') AS 'Windows-SEP-12',
(SELECT COUNT(*) 
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%') AS 'Mac-SEP-11',
(SELECT COUNT(*)
                        FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT,dbo.SEM_AGENT,dbo.PATTERN
                        WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
                        AND dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
                        AND SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
                        AND dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
                        AND Name like '%Staten Island%'
                        AND dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                        AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%')  AS 'Mac-SEP-12'

How to compress this SQL statement? Even if I use Inner Joins (which I'm working on), this statement is intense enough for the processor (not to mention that it seems really inefficient)

Answer 1

You can do this using Conditional Aggregate.

INNER JOIN . Where

SELECT Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1
             END) AS 'Windows-SEP-12',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-11',
       Count(CASE
               WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '12.%'
                    AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Mac%' THEN 1
             END) AS 'Mac-SEP-12'
FROM   dbo.sem_computer
       INNER JOIN [dbo].[V_SEM_COMPUTER]
               ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.SEM_AGENT
               ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
       INNER JOIN dbo.SEM_CLIENT
               ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.IDENTITY_MAP
               ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
       INNER JOIN dbo.PATTERN
               ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
WHERE  Name LIKE '%Staten Island%' 

Answer 2

, , ( ).

, ( ), "" .

CREATE TABLE dbo.AgentAndOsFilter
(
   Id INT IDENTITY(1, 1),
   AgentVersion VARCHAR(8),
   Os VARCHAR(16)
)

:

INSERT INTO dbo.AgentAndOsFilter (AgentVersion, Os)
VALUES ('11.%', 'Windows%'), ('12.%', 'Windows%'), ('11.%', 'Mac%'), ('12.%', 'Mac%')

:

SELECT F.Id, Count(1) Cnt
FROM   dbo.sem_computer
       INNER JOIN [dbo].[V_SEM_COMPUTER]
               ON [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.SEM_AGENT
               ON SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
       INNER JOIN dbo.SEM_CLIENT
               ON dbo.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
       INNER JOIN dbo.IDENTITY_MAP
               ON dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
       INNER JOIN dbo.PATTERN
               ON dbo.PATTERN.PATTERN_IDX = dbo.SEM_AGENT.PATTERN_IDX
       INNER JOIN dbo.AgentAndOsFilter F 
               ON dbo.SEM_AGENT.AGENT_VERSION LIKE F.AgentVersion 
                   AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE F.Os
WHERE  Name LIKE '%Staten Island%' 
GROUP BY F.Id

( , ) .