代码之家 › 专栏 › 技术社区 › Nishan256

使用内部联接联接4个表

inner-join sql-server sql

-2

Nishan256 · 技术社区 · 10 年前

我正在尝试用INNER JOIN而不是AND编写下面的查询

select ph.name, ph.position, d.name, COUNT(app.appointmentId)
from physician ph, works_in w, appointment app, department d
where ph.eid = w.physician 
    and d.did = w.department
    and ph.eid = app.physician
group by ph.eid, ph.name, ph.position, d.name

我试过这种方法,但错误太多了

select ph.name, ph.position, d.name, COUNT(app.appointmentId)
from physician ph inner join works_in w
    on ph.eid = w.department,
    department d inner join works_in w 
    on d.eid = w.department,
    physician ph inner join appointment app
    on ph.ph.eid = app.physician
group by ph.eid, ph.name, ph.position, d.name

如何使用内部联接正确地编写它。

2 回复 | 直到 10 年前

sagi 10 年前

首先让我推荐你 this site that explain all about join syntax's

将查询更改为:

select ph.name, ph.position, d.name, COUNT(app.appointmentId)
from physician ph 
inner join works_in w
    on ph.eid = w.department
inner join department d  
    on d.eid = w.department
inner join appointment app
    on ph.ph.eid = app.physician
group by ph.eid, ph.name, ph.position, d.name

连接的语法为:

SELECT <COLUMNS>
FROM <Table>
INNER JOIN <Another_Table>
 ON(<Relations>)
INNER JOIN <Another_table2>
 ON(<Other Relations>

BilalAhmed 10 年前

    SELECT ph.name AS Name, ph.position AS Position,d.name AS Name, COUNT(app.appointmentId) AS Appointment
    FROM physician AS ph INNER JOIN works_in AS w
    ON ph.eid = w.department INNER JOIN department d 
ON  d.eid = w.department INNER JOIN appointment app ON ph.eid = app.physician
    GROUP BY ph.eid,ph.name,ph.position,d.name

推荐文章

jdow · SQL noob,如何连接两个表并将数据存储到新的\u表中?

8 年前

GuillaumeRZ · 创建视图:MySQL内部联接别名

8 年前

Aakash Singh · 查询加载数据花费的时间太长

8 年前

BCH · 从Select更新-语法和概念检查(mySQL)

8 年前

David · 在中使用Sql server

8 年前

Jason Coigny · 组合多个sql语句

8 年前

J. Wolf · 如何内部联接3个表?

8 年前

WoodChuckChuck · 通过VBA将查询连接到DBF

8 年前

C. Ball · 如何使SQL内部联接绕过表?

8 年前

Bodhi · 将varchar值“T70001”转换为数据类型int时,转换失败?

8 年前