CODEWITHSUNDEEP
sqlsql-servert-sql
Actarius
Actarius

Reputation: 49

SQL SELECT; Select names associated with accounts. Three use cases

I have a Table1 as follows:

Account    Name     Flagged
-------    ----     -------
001        John        0
001        Jacob       0
002        Austin      0
002        Ashley      1
003        Mary        1
003        Megan       1

This is a simplied version of what I'm trying to figure out. Basically I have a set of Account IDs, each account ID can have basically an infinite number of names attached if it wanted. Some will have 1 name, some will have 1000 names. Each name is flagged 0 or 1.

For each account, I want to:

  1. Select all rows if all rows have flagged = 0
  2. Select all rows if all rows have flagged = 1
  3. Select only rows where flagged = 0, if rows have mixed flags. I.e. some are 0 some are 1

For this little table, I'd want it to return 

Account    Name     Flagged
-------    ----     -------
001        John        0
001        Jacob       0
002        Austin      0
003        Mary        1
003        Megan       1

Upvotes: 0

Views: 384

Answers (3)

spencer7593
spencer7593

Reputation: 108460

There are several ways to return the specified result.

Here's an example of one approach, using a NOT EXISTS predicate and a correlated subquery: 

SELECT t.account
     , t.name
     , t.flagged
  FROM [Table1] t
 WHERE t.flagged = 0 
    OR ( t.flagged = 1 AND NOT EXISTS 
                         ( SELECT 1 
                             FROM [Table1] f 
                            WHERE f.account = t.account
                              AND f.flagged = 0 
                          )
       )
 ORDER BY t.account, t.name, t.flagged

Here's an example of an approach using a join to an inline view:

SELECT t.account
     , t.name
     , t.flagged
  FROM ( SELECT n.account
              , MIN(n.flagged) AS mf
           FROM [Table1] n
          WHERE n.flagged IN (0,1)
          GROUP BY n.account
       ) s
  JOIN [Table1] t
    ON t.account = s.account
   AND t.flagged = s.mf
 ORDER BY t.account, t.name, t.flagged

EDIT: Original examples were using MySQL syntax; queries revised so syntax is compatible with SQL Server.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Gordon's solution is correct. However, you can also use MIN window function to get the minimum flag per account (assuming flagged has values 0 and 1 only) and get only those rows.

select account,name,flagged
from (select t.*,min(flagged) over(partition by account) as min_flag
      from tablename t
     ) x 
where flagged=min_flag

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is a form of prioritization query. I think the simplest approach is not exists with union all:

select t1.*
from table1 t1
where t1.flagged = 0
union all
select t1.*
from table1 t1
where t1.flagged = 1 and
      not exists (select 1
                  from table1 tt1
                  where tt1.account = t1.account and tt1.flagged = 0
                 );

The idea is simple: select all rows with a flag of 0. Then select all rows that have a flag of 1, only if there are no corresponding rows with a zero.

Upvotes: 2

Related Questions