Select rows based on values from 1 column being the same?
for selecting values based on a specific column being the same you could have a look at NTILE


  Distributes the rows in an ordered partition into a specified number
  of groups.  The groups are numbered, starting at one.  For each row,
  NTILE returns the number of the group to which the row belongs.


there are actually many options, including common_table_expression 

on your example:

--==================================================================================
use tempdb
if object_id('tempdb..radhe','U') is not null
   drop table radhe

create table  radhe(
 [Film Id] int not null
,[Scene Id] int not null
,[Value Id]int not null
)


insert into radhe ([Film Id],[Scene Id],[Value Id])
select 
   0    ,     0    ,     7
union all select 
   0    ,     1    ,     1
union all select 
   0    ,     1    ,     0
union all select 
   1    ,     2    ,     8
union all select 
   1    ,     2    ,     3
union all select 
   1    ,     3    ,     1
union all select 
   2    ,     4    ,     3
union all select 
   2    ,     4    ,     7
union all select 
   2    ,     5    ,     2



--I want to select rows 
--where the value id is equal to 8 or 3 
--only if they are both in a row with the same scene id.


--using a cte
;with t8 as (

        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (8) 
) ,
t3 as (


        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (3) 
)

select t8.* 
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]
union all
select t3.* 
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]

--====================================================================

--using NTILE
select r.[Film Id], r.[Scene Id], r.[Value Id]
from
(
SELECT 
    NTILE(2) OVER (ORDER BY [Scene Id]) [NTILE],
    *
FROM Radhe
WHERE [Value Id]IN (3,8) 
) r
where r.NTILE = 1




the results are the same but what about the performance?



check this and this out.