SQL三个表关联的查询左连接问题，求个语句？

[kukukey]

现有三个表：
sell表（商品销售表）： huiyuan表（成员表）：
id huohao price id_huiyuan id name id_zu
1 0001 30 12 12 xxx 1
2 0002 20 13 13 ccc 2

zu_huiyuan表（成员组表）
id zu
1 行政部
2 操作部
现在想以sell表中id_huiyuan为查询条件。关联成员表中id_zu及成员组表中的id
希望得到的是sell表中全部数据段信息及huiyuan表中name字段及成员组表中的zu字段
id huohao price name zu
1 0001 30 xxx 行政部
2 0002 20 ccc 操作部
请问在用到join时的条件怎么设置，求个写法。还是建个临时表，。。

[kukukey]

在问下。。那将zu_huiyuan表和huiyuan表。两表合一建立个临时表。。那该怎么做呢。

[kukukey]

这个联合查询的问题解决了。非常谢谢大家。散分了另外个问题我在起个标题。

[liangCK]

接分.

[bqb]

select * into #temp from tablename

[liangCK]

select a.id,a.huohao,a.price,b.name,c.zu
from sell a
  join huiyuan b
    on a.id_huiyuan=b.id
  join zu_huiyuan c
    on b.id_zu=c.id

[hlq8210]

select s.id,huohao,price,name,zu from sell s inner join huiyuan h
on s.id=h.id 
inner join zu_huiyuan z
on h.id=z.id

[ojuju10]

select * from sell a,huiyuan b,zu_huiyuan c
where a.id_huiyuan=b.id and b.Id_zu=c.id

[liangCK]

--左连接
select a.id,a.huohao,a.price,b.name,c.zu
from sell a
  left outer join huiyuan b
    on a.id_huiyuan=b.id
  left outer join zu_huiyuan c
    on b.id_zu=c.id

--内连接
select a.id,a.huohao,a.price,b.name,c.zu
from sell a
  join huiyuan b
    on a.id_huiyuan=b.id
  join zu_huiyuan c
    on b.id_zu=c.id

[-狙击手-]

select a.id,a.huohao,a.price,b.name,c.zu
from sell a
left  join huiyuan b
    on a.id_huiyuan=b.id
left  join zu_huiyuan c
    on b.id_zu=c.id

[liangCK]

[Quote=引用 5 楼 happyflystone 的回复:]
SQL codeselect a.id,a.huohao,a.price,b.name,c.zu
from sell a
left join huiyuan b
on a.id_huiyuan=b.id
left join zu_huiyuan c
on b.id_zu=c.id
[/Quote]

你慢了.

[ojuju10]

select a.id,a.huohao,a.price,b.name,c.zu
from sell a,huiyuan b,zu_huiyuan c 
where a.id_huiyuan=b.id and b.Id_zu=c.id


select a.id,a.huohao,a.price,b.name,c.zu
from sell a inner join huiyuan b 
on a.id_huiyuan=b.id
inner join zu_huiyuan c 
on b.Id_zu=c.id

两种写法都是一样的

[kaikai_kk]

我帮你总结::

declare @sell table (id int,huohao varchar(10),price int,id_huiyuan int)
insert into @sell
select 1,'0001',30,12 union all
select 1,'0002',22,13

 
declare @huiyuan table (id int,name varchar(10),id_zu int)
insert into @huiyuan
select 12,'xxx',1 union all
select 13,'ccc',2

declare @zu_huiyuan table (id int,zu varchar(10))
insert into @zu_huiyuan
select 1,'行政部' union all
select 2,'操作部' 

select a.*,b.name,c.zu from @sell a , @huiyuan b, @zu_huiyuan c where a.id_huiyuan=b.id
and b.id_zu=c.id

select a.*,b.name,c.zu from @sell a join @huiyuan b on a.id_huiyuan=b.id
join @zu_huiyuan c on b.id_zu=c.id

select a.*,b.name,c.zu from @sell a inner  join @huiyuan b on a.id_huiyuan=b.id
inner join @zu_huiyuan c on b.id_zu=c.id

select a.*,b.name,c.zu from @sell a left  join @huiyuan b on a.id_huiyuan=b.id
left  join @zu_huiyuan c on b.id_zu=c.id

select a.*,b.name,c.zu from @sell a left outer join @huiyuan b on a.id_huiyuan=b.id
left outer join @zu_huiyuan c on b.id_zu=c.id

/*结果

id      huohao  price   id_huiyuan  name   zu
-------------------------------------------------
1	0001	30	12	    xxx	   行政部
1	0002	22	13	    ccc	   操作部

*/

[flairsky]

select a.id,a.huohao,a.price,b.name,c.zu from sell a,huiyuan b,zu_huiyuan c where a.id_huiyuan=b.id and b.Id_zu=c.id

select a.id,a.huohao,a.price,b.name,c.zu from sell a inner join huiyuan b on a.id_huiyuan=b.id inner join zu_huiyuan c on b.Id_zu=c.id

[kukukey]

那个id_huiyuan是页面上返回的呢。。用&rs_buy("id_huiyuan").那这个在实际开发中。怎么用呢

[dawugui]

[Quote=引用 10 楼 kukukey 的回复:]
那个id_huiyuan是页面上返回的呢。。用&rs_buy("id_huiyuan").那这个在实际开发中。怎么用呢
[/Quote]
如果id_huiyuan是个变量
declare @id_huiyuan as int(你的类型自己定)
where a.id_huiyuan = @id_huiyuan

如果id_huiyuan是个字段
需要使用动态SQL来完成.

[baidutong]

好啊，我来看看。