求一条SQL语句，今天老壳有点发卡，忘了如何写_Sql Server

求一条SQL语句，今天老壳有点发卡，忘了怎么写
表1:

表2：

表1中Pid字段和表2中的Pid字段是关联的

现在想查询表2中Avid 等于11且等8的Pid，在表1中的数据。

------解决方案--------------------
select 表1.PID,表1.Name,表1.Model,表1.CID,表1.BID,表1.Mid from 表1 inner join 表2 on
表1.PID=表2.PID where 表2.AVID in(11,8)
------解决方案--------------------

SQL code

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]gocreate table [ta]([id] int,[name] varchar(1))insert [ta]select 1,'a' union allselect 2,'b' union allselect 3,'c' union allselect 4,'d'--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[name] int)insert [tb]select 1,8 union allselect 1,9 union allselect 2,8 union allselect 2,11 union allselect 3,5 union allselect 3,8 union allselect 3,11select * from [ta]where id in(select [id] from [tb] where [name] in(8,11)group by [id] having count(distinct [name])=2)