CODEWITHSUNDEEP
sqlsql-serverselect
Bebo Santana
Bebo Santana

Reputation: 25

SQL Select query when a condition is met

Site Owner |  Site URL    | Delete Site
 User #1   | google.com   |   Yes
 User #2   | google.com   |   Yes
 User #3   | yahoo.com    |   No
 User #4   | yahoo.com    |   Yes
 User #5   | hotmail.com  |   No
 User #6   | hotmail.com  |   NULL

I need to select all the Site URL where all the Site Owners have answered Yes. If one user from the same Site URL answer Yes and the other No, those I don't need. So for example I need in this example above to get google.com cause both users answer Yes.

Upvotes: 1

Views: 56

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

In simialry way you could also use of not exists

select * 
from table t 
where not exists (
    select 1 from table
    where [Site URL] = t.[Site URL]  and [Delete Site] <> 'Yes'
)

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Here is another version of doing this. For null values I preferred to use ISNULL replacer, And IIF is used for decoding Yes No to countable number

SELECT 
  [Site Url]
FROM
 Table1
GROUP BY
 [Site Url]
HAVING 
  SUM( IIF(ISNULL([Delete Site], '')='Yes', 0, 1)) = 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use group by and having:

select site_url
from t
group by site_url
having min(delete_site) = 'yes' and max(delete_site) = 'yes' and
       count(*) = count(delete_site);  -- no NULL values;

You can actually simplify the having clause to:

having count(*) = sum(case when delete_site = 'yes' then 1 else 0 end)

Upvotes: 1

Related Questions