4张表join问题

[yunhaiC QQ654777694]

有4张表 及其字段
toolsmain toolsmmain_id diff_voucherno diff_date
department_id warehouse_id machineset_id
member_id

department department_id depname
部门
machineset machineset_id machinename
机台
member member_id stuffname
人员
//////////////////////////////////////////////////////////////////////////
我这样写
select tm.diff_voucherno,tm.diff_date,dm.depname,mb.stuffname,ms.machinename
from toolsmain tm
left outer join department dm on tm.department_id = dm.department_id
left outer join member mb on tm.member_id = mb.member_id
left outer join machineset ms on tm.machineset_id = ms.machineset_id
where ms.machinename='1#'
////////////////////////////////////////////////////////////////////////////

1#代表的是一号机台
我的意思就是分别想从四张表中取得diff_voucherno,diff_date,depname,stuffname,machinename
这几个字段，但是要对应的是 1# 机台
我执行select一直到最后一个left outer join 结果是这样的
diff_vouchno diff_date depname stuffname machinename
FT2008101401 20081014 仓库 胡汉三 NULL

我现在就是加where的条件要显示一号机台的内容不行，理想中的情况是
diff_vouchno diff_date depname stuffname machinename
FT2008101401 20081014 仓库 胡汉三 1#
当然可能有多笔记录

请教这个问题怎么解决？

[liangCK]

真不是一般的乱.

[水族杰纶]

很乱

[yunhaiC QQ654777694]

不好意思

有4张表 及其字段
toolsmain toolsmmain_id diff_voucherno diff_date
department_id warehouse_id machineset_id
member_id

department department_id depname
部门
machineset machineset_id machinename
机台
member member_id stuffname
人员

[等不到来世]

select tm.diff_voucherno,tm.diff_date,dm.depname,mb.stuffname,machinename=isnull(ms.machinename,'1#')
from  toolsmain tm 
left outer join department dm on tm.department_id = dm.department_id 
left outer join member mb on tm.member_id = mb.member_id 
left outer join machineset ms on tm.machineset_id = ms.machineset_id 

[ws_hgo]

/*
toolsmain      toolsmmain_id    diff_voucherno  diff_date 
              department_id    warehouse_id    machineset_id 
              member_id 
department      department_id  depname 
部门 
machineset      machineset_id    machinename 
机台 
member          member_id        stuffname 
*/
select tool.diff_voucherno,tool.diff_date,d.depname,m.machinename,Member.stuffname from toolsmain tool join
department d on tool.department_id=d.department_id join machineset m on m.machineset_id=tool.machineset_id join  member
Member on Member.member_id=tool.member_id  

[ws_hgo]

LZ
我肯请你
以后发贴
把东西搞清楚
你搞的那样
别人都不好帮你的
你看人家小梁 MM
都不想看啦