CODEWITHSUNDEEP
sqlsql-servert-sql
Guilherme Ferreira
Guilherme Ferreira

Reputation: 1613

How to return all rows if IN subquery has no rows?

Following is sample query

Select *
  from tb.Users u
 where u.Approved = 1
   and u.userID IN ( SELECT us.UserID us
                       FROM tb.UserStatementes us
                      WHERE us.LogDate between date1 and date2 )

I need to select all users that are in th IN clause however if the subquery has not returned records I need to select all users (ie ignore the IN clause)

Upvotes: 0

Views: 110

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Select *
  from tb.Users u
 where u.Approved = 1
   and ( exists        ( SELECT 1 
                           FROM tb.UserStatementes us
                           WHERE us.LogDate between date1 and date2 
                             AND us.USERID = u.USERID )
         or not exists ( SELECT 1 
                           FROM tb.UserStatementes us
                          WHERE us.LogDate between date1 and date2 )
       )

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Select *
  from tb.Users u
 where u.Approved = 1
   and (
    u.userID IN ( SELECT us.UserID us
                        FROM tb.UserStatementes us
                       WHERE us.LogDate between date1 and date2 
                       )
    or not exists (
        select 1
          from tb.UserStatementes us
          where us.LogDate between date1 and date2
        )
      )

Upvotes: 6

Related Questions