Select sub-table on condition

Analytics

Select sub-table on condition

For example, i want to filter all data for people, which contain "video"

Johnvideo2
Johnaudio1
Johnsoft1
Petervideo3
Peteraudio4
Maryaudio1
Marysoft5
Georgevideo2
Georgeaudio4
Gregorysoft3

Need this one 

Johnvideo2
Johnaudio1
Johnsoft1
Petervideo3
Peteraudio4
Georgevideo2
Georgeaudio4

Pls help me


Accepted Solutions
Senior Apprentice

Re: Select sub-table on condition

There are two common ways:

select *
from tab
qualify
   max(case when col2 = 'video' then 1 end)
   over (partition by col1) = 1

select *
from tab as t1
where exists
 ( select * from tab as t2
   where t1.col1 = t2.col1
   and t2.col2 = 'video'
 )
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Select sub-table on condition

There are two common ways:

select *
from tab
qualify
   max(case when col2 = 'video' then 1 end)
   over (partition by col1) = 1

select *
from tab as t1
where exists
 ( select * from tab as t2
   where t1.col1 = t2.col1
   and t2.col2 = 'video'
 )

Re: Select sub-table on condition

Thx U, that's great!