CODEWITHSUNDEEP
sqlsql-servert-sql
Andrew Jocelyn
Andrew Jocelyn

Reputation: 589

SQL if else filter in single select statement

Is it possible to output the following:

for each group by personId, if email ends with '@company.com' then filter by [where type = 'h'] else filter [where type = 't'] so the query would output the following:

1 [email protected] h

2 [email protected] t

create table #emails (personId int, email nvarchar(100), type char(1) )

insert into #emails values (1, '[email protected]', 't');
insert into #emails values (1, '[email protected]', 'h');
insert into #emails values (2, '[email protected]', 't');
insert into #emails values (2, '[email protected]', 'h');

select * from #emails

drop table #emails

Thanks

Upvotes: 0

Views: 917

Answers (4)

iSR5
iSR5

Reputation: 3498

If I get it right. Your @company.com is a default value to email column. So, you want to get the actual email, which will be the next email for each person. I'm not sure about the type column why you're trying to using it, as (in my opinion) doesn't matter if is it h or t as long as we can exclude emails that ends with @company.com directly with this simple query : 

SELECT *
FROM  #emails
WHERE 
    RIGHT(email, 11) <> 'company.com'

from there you can expand the where clause as much conditions as needed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271161

This is based on the description of the logic, not the sample results.

I think you want boolean logic in the where clause:

where (email like '%@company.com' and type = 'h') or
      (email not like '%@company.com' and type = 't')

This particular version assumes that email is never NULL. It is easy enough to incorporate that into the logic.

EDIT:

I see, this is a prioritization query:

select top (1) with ties e.*
from #emails e
order by row_number() over (partition by personId
                            order by (case when email like '%@company.com' and type = 'h' then 1
                                           when type = 't' then 2
                                           else 3
                                      end)
                            );

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82534

It's kinda hard to understand the question, but I think you are looking for something like this:

SELECT personId, email, type
FROM #emails t0
WHERE type = CASE WHEN EXISTS(
    SELECT 1
    FROM #emails t1
    WHERE t0.personId = t1.personId
    AND t1.email LIKE '%@company.com'
) THEN 'h' ELSE 't' END

This will give the desired results, so the text of the question should be something like "when there is a record for that person with email ends with @company.com then type h otherwise type t.

Upvotes: 0

stubs
stubs

Reputation: 274

You can exclude type column from the insert and use a computed field instead, as below:

create table #emails (personId int, email nvarchar(100), type as case when email like '%@company.com' then 'h' else 't' end)

Like that all your inserts will automatically have the type column handled

EDIT: If you still want to perform an update after words just use the same CASE statement in the select.

Upvotes: 0

Related Questions