假如有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,请问该怎么写查询语句呢?
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,请问该怎么写查询语句呢?
FROM t1
WHERE id IN ( SELECT id
FROM t2
WHERE [type] IN ( 1, 2 ) )
FROM t1
WHERE id IN ( SELECT *
FROM ( SELECT id
FROM [t2]
WHERE [type] = 1
UNION
SELECT id
FROM [t2]
WHERE [type] = 2
) 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
)
select a.name,b.type from
t1 a,t2 b
where a.id =b.id and b.type in (1,2)
INTERSECT
SELECT id FROM [t2] WHERE [type] = 2