两表的关联查询

[lutianling521]

A: id1 id2
1 2
1 3
2 2
2 3
2 4
B: id img .......
1 1.jpg .......
2 2.jpg .......
3 3.jpg .......
......

根据id1查询出B表的信息比如说id1=1就查出B表的id为2和3的所有列信息!

[htl258_Tony]

select b.* from a join b on a.id2=b.id and a.id=1

[htl258_Tony]

if object_id('a') is not null
  drop table a
go
create table a([id1] varchar(10),[id2] varchar(10))
insert a select 1,'2'
insert a select 1,'3'
insert a select 2,'2'
insert a select 2,'3'
insert a select 2,'4'
go
if object_id('b') is not null
  drop table b
go
create table b([id] varchar(10),[img] varchar(10))
insert b select '1','1.jpg'
insert b select '2','2.jpg'
insert b select '3','3.jpg'

go

select b.* from a join b on a.id2=b.id and a.id1=1
/*
id         img
---------- ----------
2          2.jpg
3          3.jpg

(2 行受影响)
*/

[htl258_Tony]

也可以用：

select b.* from a join b on a.id2=b.id where a.id1=1
select b.* from a,b where a.id2=b.id and a.id1=1