急求关于left join on ,在线等候

[Justin1818]

有以下表
a <客户表
vendor_id
vendor_ename
vendor_cname
....

b
visit_uid
....
visit_vdid ---->与 a.vendor_id相联
visit_utid ---->与 d.user_id相联


c <客户别名表
vm_ename
vm_cname
vm_vdid ---->与 a.vendor_id相联

d
user_id
user_name

如何实现以下查询 b表 满足以下条件: 注意 是B表
vender name: _____ < 根据vendor 的名称,同时检索别名表 (同时检索中英文名) >



谢谢

[古侠]

只有帮你顶一下了

[Justin1818]

补:
如何实现以下查询 b表 满足以下条件: 注意 是B表
vender name: _____ < 根据vendor 的名称,同时检索别名表 (同时检索中英文名) >
user name ______

[alern_zyb]

select b.*,c.* from a,b,c where a.vendor_id=b.visit_vdid and a.vendor_id=c.vm_vdid and (a.cname like %中文名% or a.ename like %英文名%)

[yb2008]

同意楼上的!

[Justin1818]

在testing...

[Justin1818]

还是不行,
是动太查询,如果用户不是按客户名查询以上就不适合
a----指 VENDOR_TAB
b----指 VDVISIT_TAB
c----指 VENDORMUL_TAB
d----指 USER_TAB
我想

VENDOR_TAB
VD_ID
VD_ENAME
VD_CNAME
.......

VENDORMUL_TAB
VENDORMUL_ID
VENDORMUL_ENAME
VENDORMUL_ENAME


VDVISIT_TAB
VDVISIT_ID
VDVISIT_VDID ----- 对应 VENDOR_TAB.VD_ID
VDVISIT_UID ----- 对应 USER_TAB.USER_ID

USER_TAB
USER_ID
USER_ENAME
USER_CNAME
USER_POSITION
.....

sqlA="select * From VDVISIT_TAB"

<!----------------------- if search by vendor name -------------------------------->
sqlb="left join VENDOR_TAB on VDVISIT_TAB.VDVISIT_ID=VENDOR_TAB.VD_ID"_
&"left join VENDORMUL_TAB on VDVISIT_TAB.VDVISIT_ID=VENDORMUL_TAB.VENDORMUL_VDID"
sqlc=" where ( VENDOR_TAB.VD_ENAME like '% vendorname_param %' or "_
&" VENDOR_TAB.VD_CNAME like '% vendorname_param %' or "_
&" VENDORMUL_TAB.VENDORMUL_ENAME like '% vendorname_param %' or "_
&" VENDORMUL_TAB.VENDORMUL_CNAME like '% vendorname_param %' ) "
<!----------------------------end of search by vendor name -------------------->

<!-----------------------if search by User name ----------------------------->
sqlb=sqlb & " left join USER_TAB on VDVISIT_TAB.VDVISIT_UID=USER_TAB.USER_ID "
<!---- if sqlc is empty then ---->
sqlc=sqlc & " where USER_TAB.USER_NAME like '% username_param %'
<!-----else if sqlc is not empty then --->
sqlc=sqlc & " and USER_TAB.USER_NAME like '% username_param %'
<!----------------------------end search by user name ------------------------>

[Justin1818]

----不好意思,前面是不小心按错键发出的

还是不行,
是动太查询,如果用户不是按客户名查询以上就不适合
a----指 VENDOR_TAB
b----指 VDVISIT_TAB
c----指 VENDORMUL_TAB
d----指 USER_TAB
我想

VENDOR_TAB 客户信息表
VD_ID
VD_ENAME
VD_CNAME
.......

VENDORMUL_TAB 客户别名表
VENDORMUL_ID
VENDORMUL_ENAME
VENDORMUL_CNAME
VENDORMUL_VDID ----- 对应 VENDOR_TAB.VD_ID


VDVISIT_TAB 拜访客户的信息表
VDVISIT_ID
VDVISIT_VDID ----- 对应 VENDOR_TAB.VD_ID
VDVISIT_UID ----- 对应 USER_TAB.USER_ID < 用户ID

USER_TAB 用户
USER_ID
USER_ENAME
USER_CNAME
USER_POSITION
.....


这张表是我要显示的
'--------------------------------------------------------
'VDVISIT_TAB '
' VDVISIT_ID '
' VDVISIT_VDID ----- 对应 VENDOR_TAB.VD_ID '
' VDVISIT_UID ----- 对应 USER_TAB.USER_ID '
'--------------------------------------------------------

sqlA="select * From VDVISIT_TAB"

<!----------------------- if search by vendor name -------------------------------->
sqlb="left join VENDOR_TAB on VDVISIT_TAB.VDVISIT_ID=VENDOR_TAB.VD_ID"_
&"left join VENDORMUL_TAB on VDVISIT_TAB.VDVISIT_ID=VENDORMUL_TAB.VENDORMUL_VDID"
sqlc=" where ( VENDOR_TAB.VD_ENAME like '% vendorname_param %' or "_
&" VENDOR_TAB.VD_CNAME like '% vendorname_param %' or "_
&" VENDORMUL_TAB.VENDORMUL_ENAME like '% vendorname_param %' or "_
&" VENDORMUL_TAB.VENDORMUL_CNAME like '% vendorname_param %' ) "
<!----------------------------end of search by vendor name -------------------->

<!-----------------------if search by User name ----------------------------->
sqlb=sqlb & " left join USER_TAB on VDVISIT_TAB.VDVISIT_UID=USER_TAB.USER_ID "
<!---- if sqlc is empty then ---->
sqlc=sqlc & " where USER_TAB.USER_NAME like '% username_param %'
<!-----else if sqlc is not empty then --->
sqlc=sqlc & " and USER_TAB.USER_NAME like '% username_param %'
<!----------------------------end search by user name ------------------------>

[lstup]

关注ing。。。

[jzd1997]

select b.visit_vid,b.visit_uid,a.vendor_ename,a.vendor_cname,d.user_name
from b left join a on (b.visit_vid=a.vendor_id and
(a.vendor_ename like '%中英文名%' or a.vendor_cname like '%中英文名%'))
left join d on (b.visit_utid=d.user_id and d.user_name like '%中英文名%')

[jzd1997]

绝对可以通过一条SQL解决的，给你点信心，慢慢调

[jzd1997]

对了不要把你的用户名条件放到最后，否则就变成inner join on那个条件了，就很容易选不出来的

[mqjshanghai]

不懂，顶

[gu1dai]

简单：你的字段乱的很。
大概是这样

select * from
b left join c
on b.id=c.id
left join d
on b.id=d.id
left join e
on b.id=e.id

where e.emname like '%...%' --注意这里，这里才是客户提交的要查询的别名，注意关键字，id不能有重复的，这点是必须的，否则不管你是外联还是内联都会出重复的记录

[gu1dai]

注意省略号处才是你要填写的关键字

[gu1dai]

你研究研究 不明白再pm我

[jzd1997]

用子查询可能比较容易理解些
select b.visit_vid,b.visit_uid,a.vendor_ename,a.vendor_cname,d.user_name
from b left join (select * from a where a.vendor_ename like '%中英文名%' or a.vendor_cname like '%中英文名%') a
on b.visit_vid=a.vendor_id
left join (select * from d where d.user_name like '%中英文名%') d on b.visit_utid=d.user_idre d.user_name like '%中英文名%') d on b.visit_utid=d.user_id
left join (select * from c where c.vm_ename like '%中英文名%'
or c.vm_cname like '%中英文名%') c on c.vm_vdid=a.vendor_id