求一个组合查询条件的查询语句

[星月无痕]

假如有2个表t1（商品表）和t2（商品所属的属性表），
t1字段和数据：
id name
1 n1
2 n2
3 n3
.......

t2字段和数据：
id type
1 1
1 2
1 3
2 1
2 2
3 1
4 3
.......

现在要查询出一些商品，既属于type1也属于type2，请问该怎么写查询语句呢？

[哥眼神纯洁不]

select id from t2 where type in (1,2) group by id having count(1)=2

[發糞塗牆]

SELECT  *
FROM    t1
WHERE   id IN ( SELECT  id
                FROM    t2
                WHERE   [type] IN ( 1, 2 ) )

[星月无痕]

引用 2 楼 DBA_Huangzj 的回复:

SQL code?12345SELECT *FROM t1WHERE id IN ( SELECT id FROM t2 WHERE [type] IN ( 1, 2 ) )

这个有问题吧，这样查询的是属于1或者属于2，而不是既属于1又属于2

[發糞塗牆]

SELECT  *
FROM    t1
WHERE   id IN ( SELECT  *
                FROM    ( SELECT    id
                          FROM      [t2]
                          WHERE     [type] = 1
                          UNION
                          SELECT    id
                          FROM      [t2]
                          WHERE     [type] = 2
                        ) a )

[我腫了]

select * from t1 As a
Where Exists(select 1 from t2 As x
                 Where x.id=a.id 
                     And x.type=1
         )
    And Exists(select 1 from t2 As x
                 Where x.id=a.id 
                     And x.type=2
             )

[Paddy]

select a.name,b.type from
t1 a,t2 b
where a.id =b.id and b.type in (1,2)

[星月无痕]

我查了一下，好像INTERSECT语句可以解决这个问题...期待更多人解答！

[發糞塗牆]

4楼的不行？

[szm341]

除了2L跟6L的都行呵呵~

SELECT    id   FROM      [t2]  WHERE     [type] = 1
INTERSECT
SELECT    id   FROM      [t2]  WHERE     [type] = 2

[码农阿呆]

引用 5 楼 bbbbbben 的回复:

SQL code ? 123456789 select * from t1 As a Where Exists(select 1 from t2 As x Where x.id=a.id And x.type=1 ) And Exists(select 1 from t2 ……

我觉得5楼的可以把...