CODEWITHSUNDEEP
sql-server
JSK
JSK

Reputation: 5

SQL Server : extract only records that meet specific conditions

I have two tables in SQL Server as shown here:

[table - a]

cod name
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff
7 ggg
8 hhh

[table - b]

cod location auth
1 1001 1
2 1002 0
3 1003 null
4 1002 1
4 1003 1
5 1001 0
5 1003 null
6 1002 0
6 1001 0
7 1003 1
7 1002 null
8 1001 1
8 1003 0

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

If there are multiple locations corresponding to the code

The result should look like this:

cod name location auth
2 bbb 1002 0
3 ccc 1003 null
4 ddd 1002 1
4 ddd 1003 1
5 eee 1001 0
5 eee 1003 null
6 fff 1002 0
6 fff 1001 0
7 ggg 1003 1
7 ggg 1002 null

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.

Upvotes: 0

Views: 60

Answers (1)

ash
ash

Reputation: 3085

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'

Upvotes: 0

Related Questions