I have two tables in SQL Server as shown here:

[table - a]

[table - b]

I join the above two tables to extract records, but I want only the records that meet the conditions below to be displayed.

If there is only one location corresponding to the code

• Extract unconditionally if auth is not '1'

If there are multiple locations corresponding to the code

• Extract the code where there are two or more of which auth is '1'.
• Extract the code where there is only one auth equal to '1' but null exists
• Extract the code without any auth equal to '1'.

The result should look like this:

My query is below now.

WITH T AS 
(
    SELECT 
        a.cod, b.location, b.auth,
        ROW_NUMBER() OVER(PARTITION BY a.cod ORDER BY b.location) COL
    FROM 
        a 
    LEFT JOIN 
        b ON a.cod = b.cod
    GROUP BY 
        a.cod, b.location, b.auth
) 
SELECT DISTINCT(cod), location, auth, COL, CHK 
FROM (SELECT T.cod, T.location, T.auth, T.COL,
CASE WHEN MAX(T.COL) OVER(PARTITION BY T.cod)=1 AND T.auth=1 THEN 'S-AUTH'
     WHEN MAX(T.COL) OVER(PARTITION BY T.cod)=1 AND T.auth=0 THEN 'S-NONE'
     WHEN MAX(T.COL) OVER(PARTITION BY T.cod)=1 AND T.auth IS NULL THEN 'S-DEFAULT'
     WHEN MAX(T.COL) OVER(PARTITION BY T.cod)<>1 AND T.auth=1 THEN 'M-AUTH'
     WHEN MAX(T.COL) OVER(PARTITION BY T.cod)<>1 AND T.auth=0 THEN 'M-NONE'
     WHEN MAX(T.COL) OVER(PARTITION BY T.cod)<>1 AND T.auth IS NULL THEN 'M-DEFAULT'
     ELSE 'N'
     END AS CHK
 FROM T) A   
 ORDER BY cod ASC

Please advise and help.

CodePudding user response：

If you want the output you mentioned above, use the following query

with t as
         (
             select a.cod, a.name, b.location, b.auth,
                    sum(b.auth) over (partition by b.cod) as AuthSum,
                    max(case when b.auth is null then 1 else 0 end) over (partition by b.cod) as ContainsNull
             from a
             join b ON a.cod = b.cod
         )
select cod, name, location, auth
from t
where (AuthSum >= 2) --Extract the code where there are two or more of which auth is '1'.
    OR (AuthSum = 1 and ContainsNull = 1) --Extract the code where there is only one auth equal to '1' but null exists
    OR (AuthSum is null or AuthSum = 0)  --Extract unconditionally if auth is not '1' OR Extract the code without any auth equal to '1'