Grouped IF statement in SQL-CodePudding

My data take this basic shape: (http://sqlfiddle.com/#!9/d4ae98/1)

CREATE TABLE Table1
(`ID` int, `Type` varchar(1));

INSERT INTO Table1
(`ID`, `Type`)
VALUES
(123, 'A'),
(123, 'B'),
(123, 'C'),
(456, 'A'),
(789, 'A'),
(789, 'B')
;

What I want is, a third column which is true/false for every row, based on whether that row's ID value has type='B' anywhere in the data. So the desired output would be:

ID   Type   V3
123  A      t
123  B      t
123  C      t
456  A      f
789  A      t
789  B      t

What is the best way to do this? (And, yes, I am aware that a scripting language like R or Python could easily do what I want here, but I want to use this output as a WITH clause in a larger SQL query.)

CodePudding user response：

You can do this with a Case in the Select:

select *, CASE
WHEN id in (select id from table1 where type like '%B%') then 't'
ELSE 'f'
END V3
from table1;

Fiddle link: http://sqlfiddle.com/#!9/e47bc37/1

CodePudding user response：

May a solution like this one can help you:

with Table2 as (
select * from table1 where type ='B'
)
select t1.*, case t2.type when 'B' then 't' else 'f' end v3 from table1 t1 left 
outer join table2 t2 on t1.id = t2.id ;