在Cisco CUCM的informix中连接多个表

Question

我有两个问题和他们的结果如下-

1)

select a.pkid,a.name,c.buttonnum,c.label from device a, phonetemplate b, phonebutton c where a.name='SEPE8BA70FA173D' and a.fkphonetemplate=b.pkid and c.fkphonetemplate=b.pkid and (c.tkfeature=9 or c.tkfeature=2) and c.label like 'Speed Dial%'

pkid      name            buttonnum label
=====     ==============  ========= ============

6c9a81b4 SEPE8BA70FA173D  4         Speed Dial 2
6c9a81b4 SEPE8BA70FA173D  5         Speed Dial 3
6c9a81b4 SEPE8BA70FA173D  6         Speed Dial 4
6c9a81b4 SEPE8BA70FA173D  3         Speed Dial 1
6c9a81b4 SEPE8BA70FA173D  7         Speed Dial 5

2)选择

 a.pkid,a.name,d.speeddialindex,d.speeddialnumber,d.label,d.labelascii from device a,speeddial d where d.fkdevice=a.pkid and a.name='SEPE8BA70FA173D';

pkid       name            speeddialindex speeddialnumber label labelascii
========  ===============  ============== =============== ===== ==========

6c9a81b4  SEPE8BA70FA173D  2              *69777777732#   Umesh
6c9a81b4  SEPE8BA70FA173D  4              2000            A     A
6c9a81b4  SEPE8BA70FA173D  5              2002            B     B

我想在上面的两个查询中留下连接，这样我的结果就是a。

pkid                                 a.name        d.speeddialindex d.speeddialnumber d.label d.labelascii   c.label
========== =============== ============== ===============     ===== ==========       ==========

6c9a81b4   SEPE8BA70FA173D 2              *69777777732#      Umesh                   Speed Dial 2
6c9a81b4   SEPE8BA70FA173D 4              2000               A      A                Speed Dial 4
6c9a81b4   SEPE8BA70FA173D 5              2002               B      B                Speed Dial 5
6c9a81b4   SEPE8BA70FA173D 3                                                         Speed Dial 3
6c9a81b4   SEPE8BA70FA173D 1                                                         Speed Dial 1

我在下面尝试过，但遇到语法错误

select
    a.pkid pkid1,
    a.name,
    c.buttonnum,
    c.label,
    d.speeddialindex,
    d.speeddialnumber,
    d.label,
    d.labelascii,
    d.fkdevice fkdevice1
from device a,
     phonetemplate b,
     phonebutton c
where a.name='SEPE8BA70FA173D'
    and a.fkphonetemplate=b.pkid
    and c.fkphonetemplate=b.pkid
    and (c.tkfeature=9 or c.tkfeature=2)
    and c.label like 'Speed Dial%'
LEFT JOIN speeddial d ON pkid1 =fkdevice1

致以敬意，Umesh

Answer 1

首先，你是mixing ANSI-style joins with old-style。第二，LEFT JOIN不在正确的位置，必须在WHERE子句之前加入。

下面是ANSI风格的查询，检查它是否工作

select
    a.pkid pkid1,
    a.name,
    c.buttonnum,
    c.label,
    d.speeddialindex,
    d.speeddialnumber,
    d.label,
    d.labelascii,
    d.fkdevice fkdevice1
from device a
join phonetemplate b on a.fkphonetemplate=b.pkid
join phonebutton c on c.fkphonetemplate=b.pkid
left join speeddial d ON a.pkid = d.fkdevice1
where a.name='SEPE8BA70FA173D'
    and (c.tkfeature=9 or c.tkfeature=2)
    and c.label like 'Speed Dial%'