select  * from 表1 as a 
where exists(select 1 from 表2 where PID=a.PID and AVID=11)
and exists(select 1 from 表2 where PID=a.PID and AVID=8

------解决方案--------------------

SQL code
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(1))
insert [ta]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d'


--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] int)
insert [tb]
select 1,8 union all
select 1,9 union all
select 2,8 union all
select 2,11 union all
select 3,5 union all
select 3,8 union all
select 3,11

select * from [ta]
where id in(
select [id] from [tb] where [name] in(8,11)
group by [id] having count(distinct [name])=2
)